Announcement

Collapse
No announcement yet.

Indexes on SQL server D_MM tables

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Indexes on SQL server D_MM tables

    I've been looking at the indexes on some of the Mirth tables. I noticed that all the D_MM tables have 5 indexes. Some of them are redundant, which means that they are taking more storage than necessary and causing a performance penalty for no gain.

    For example, if I look at table D_MM1, the indexes are:

    D_MM1_PKEY
    MESSAGE_ID ASC
    ID ASC

    D_MM1_FKI
    MESSAGE_ID ASC

    D_MM1_INDEX1
    MESSAGE_ID ASC,
    ID ASC,
    STATUS ASC

    D_MM1_INDEX2
    MESSAGE_ID ASC
    SERVER_ID ASC
    ID ASC

    D_MM1_INDEX3
    ID ASC
    STATUS ASC
    SERVER_ID ASC


    Index D_MM1_FKI is redundant because D_MM1_PKEY already has MESSAGE_ID as the first column in the index, as do other indexes.

    Index D_MM1_INDEX1 looks to be a waist because it is indexes the MESSAGE_ID, ID and STATUS columns. D_MM1_PKEY already indexes MESSAGE_ID, ID columns but adding Status to the index won't buy you anything because you need to specify MESSAGE_ID and ID in your query to make use of the index. If you do that, you'll only get one record back anyhow. Removing this index and using D_MM1_PKEY would be a better option.


    Index D_MM1_INDEX2 looks to be poorly optimized because it indexes columns MESSAGE_ID, SERVER_ID, and ID. ID doesn't need to be in the index. For performance reasons you may need to add it as an included column in the index, but not actually as an indexed column.

    Index D_MM1_INDEX3 looks to be poorly optimized because it indexes columns ID, STATUS, and SERVER_ID. To use the index, the query needs to specify the value for ID, which would only return 1 record, so there's no point indexing STATUS and SERVER_ID. For performance reasons you may need to add those columns as included columns in the index, but not actually as indexed columns.

    The above suggestions are just based on looking at the data, not analyzing the queries that Mirth uses, but I'm pretty sure these suggestions would improve performance and reduce load.

    Also, I suspect that an index on RECEIVED_DATE that possibly has CONNECTOR_NAME as an included column would be beneficial.
Working...
X