web stats
Indexes on SQL server D_MM tables - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 08-23-2016, 09:00 AM
jerchap jerchap is offline
OBX.3 Kenobi
 
Join Date: Nov 2007
Location: British Columbia, Canada
Posts: 128
jerchap is an unknown quantity at this point
Default 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.
Reply With Quote
Reply

Tags
enhancement, indexing, performance

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 03:27 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation