MySQL processlist shows that there are many threads running the same slow query, and the back log continue to grow...
Looking at SQL, it is not complex:
SELECT COUNT(*) FROM tblA WHERE tblA.fkA_ID = 38926722 AND tblA.fkAType_ID = 1 AND tblA.fkADetail_ID = 476 AND tblA.`Date` = '2013-05-10 07:14:41' AND tblA.Time = '2013-05-10 07:14:41'
And the EXPLAIN output is:
id: 1
select_type: SIMPLE
table: tblA
type: index_merge
possible_keys: FK_A_ID,FK_fkAType_ID,FK_fkADetail_ID,IX_Date
key: FK_ApmAudit2ApmCase,FK_ApmAudit2ApmAuditType
key_len: 4,1
ref: NULL
rows: 1
Extra: Using intersect(FK_A_ID,FK_fkAType_ID); Using where
1 row in set (0.00 sec)
At first glance, it does not look too bad. MySQL is using two different indexes to search 1 out of 66M rows to return the count of total rows. It is worth mentioning here that column fkAType_ID is not very selective i.e. it holds value 1 or 2. Sadly, 90% of the rows had value 1. This means query is looking at millions of rows before doing index merge with column fkA_ID.
Ok, we know now what's wrong with the query, let's see what options do we have to to optimize this query:?
A) The table hasn't been purged for the past several months; this couldn't be done at this time
B) Pass index hints to the query optimizer to favor index on fkA_ID column.
SELECT COUNT(*) FROM tblA USE INDEX (FK_A_ID) WHERE tblA.fkA_ID = 38926722 AND tblA.fkAType_ID = 1 AND tblA.fkADetail_ID = 476 AND tblA.`Date` = '2013-05-10 07:14:41' AND tblA.Time = '2013-05-10 07:14:41' Query OK, 1 row affected (0.20 sec)
Query completed in a fraction of a second, compared to 30 seconds! Lets look at EXPLAIN output:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tblA
type: ref
possible_keys: FK_A_ID
key: FK_A_ID
key_len: 4
ref: const
rows: 179
Extra: Using where
1 row in set (0.00 sec)
Once the backlog consumed, in the next available maintenance window, we purged historic data, removed index hints and added following index to the table:
ALTER TABLE tblA DROP INDEX `FK_A_ID`, ADD INDEX `FK_A_ID_DATE` (`fkA_ID`,`Date`)Note: index on column FK_fkAType_ID is needed due to foreign key constraint. The table structure info:
CREATE TABLE `tblA` ( `pkID` int(10) unsigned NOT NULL AUTO_INCREMENT, `fkApplication_ID` smallint(5) unsigned NOT NULL, `fkA_ID` int(10) unsigned NOT NULL, `fkAType_ID` tinyint(3) unsigned NOT NULL, `Date` date NOT NULL, ... KEY `FK_Application` (`fkApplication_ID`), KEY `FK_fkAType_ID` (`fkAType_ID`), KEY `IX_Date` (`Date`), KEY `FK_A_ID` (`fkA_ID`), ... CONSTRAINT `FK_AType` FOREIGN KEY (`fkAType_ID`) REFERENCES `tblAType` (`pkAType_ID`), ... ) ENGINE=InnoDB AUTO_INCREMENT=505970021 DEFAULT CHARSET=utf8
