Mirth Performance Issue

1. Too many indexes are killing the database performance.



MESSAGE table have the following indexes:



KEY `MESSAGE_INDEX1` (`CHANNEL_ID`,`DATE_CREATED`),

KEY `MESSAGE_INDEX2` (`CHANNEL_ID`,`DATE_CREATED`,`CONNECTOR_NAME`),

KEY `MESSAGE_INDEX3` (`CHANNEL_ID`,`DATE_CREATED`,`RAW_DATA_PROTOCOL`),

KEY `MESSAGE_INDEX4` (`CHANNEL_ID`,`DATE_CREATED`,`SOURCE`),

KEY `MESSAGE_INDEX5` (`CHANNEL_ID`,`DATE_CREATED`,`STATUS`),

KEY `MESSAGE_INDEX6` (`CHANNEL_ID`,`DATE_CREATED`,`TYPE`),

KEY `MESSAGE_INDEX7` (`CORRELATION_ID`,`CONNECTOR_NAME`),

KEY `MESSAGE_INDEX8` (`ATTACHMENT`),



After dropping 7 indexes( 1,2,3,4,5,7,8), I/O performance improved from 75% to 35%.



Question : Do we need all the indexes? if Yes, why.





2. Mirth is using insert which is causing performance issue for too may inserts.



Question : Can you use Load data infile which provides 20x times faster performance for data load?

Mirth Performance Issue