Mysql Query takes forever after adding a where condition

MySQLoptimizationperformancequery-performance

I have a mysql query which return results in 4 seconds. 

SELECT 
    *
FROM
    xercasehistory
WHERE
    xerId = 192             
ORDER BY id DESC limit 10;

But when I add extra where parameters to the existing one the it is taking 30 seconds approx to complete. 

SELECT 
    *
FROM
    xercasehistory
WHERE
    xerId = 192 AND type = 'case'
        AND fieldName = 'statusCode'
        AND typesKey=5            
ORDER BY id DESC limit 10;

So, I check the indexes from on same table by running show indexes from xercasehistory

xercasehistory  0   PRIMARY                     1   id         A    24545933                BTREE
xercasehistory  1   xecasehistory_caseId_IDX    1   caseId     A    2045494             BTREE
xercasehistory  1   xecasehistory_typeskey      1   typesKey    A   20169           YES BTREE
xercasehistory  1   xecasehistory_active_govtId 1   active      A   2               BTREE
xercasehistory  1   xecasehistory_active_govtId 2   govtId      A   39336               BTREE
xercasehistory  1   xecasehistory_type          1   type        A   7721                BTREE
xercasehistory  1   xercasehistory_entryDate    1   entryDate   A   24545933                BTREE
xercasehistory  1   xercasehistory_fieldName    1   fieldName   A   14304           YES BTREE
xercasehistory  1   xercasehistory_recordTable  1   recordTable A   11406           YES BTREE
xercasehistory  1   xerId                       1   xerId       A   47113           YES BTREE
xercasehistory  1   govtId                      1   govtId      A   34329               BTREE

We can see an index in all fields used in where clause (xerId,type,type,typesKey ). Now the question is I was expecting a shorter wait time because it already filter data from xerId = 192 so the remaining where conditions should work on records already filtered by xerId = 192. Please help

Output of SHOW CREATE TABLE xercasehistory; :

CREATE TABLE `xercasehistory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `govtId` int(11) NOT NULL DEFAULT '0',
  `caseId` int(11) NOT NULL DEFAULT '0',
  `type` varchar(100) NOT NULL DEFAULT '',
  `notes` text NOT NULL,
  `entryDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `employeeId` int(11) NOT NULL DEFAULT '0',
  `typesKey` int(7) DEFAULT '0',
  `logLevel` int(11) NOT NULL DEFAULT '1000',
  `dataType` int(11) NOT NULL DEFAULT '0',
  `recordTable` varchar(100) DEFAULT NULL,
  `old_recordId` int(11) DEFAULT NULL,
  `new_recordId` int(11) DEFAULT NULL,
  `fieldName` varchar(100) DEFAULT NULL,
  `old_int` int(11) DEFAULT NULL,
  `old_varchar` varchar(255) DEFAULT NULL,
  `old_text` text,
  `old_float` float DEFAULT NULL,
  `old_date` datetime DEFAULT NULL,
  `new_int` int(11) DEFAULT NULL,
  `new_varchar` varchar(255) DEFAULT NULL,
  `new_text` text,
  `new_float` float DEFAULT NULL,
  `new_date` datetime DEFAULT NULL,
  `more_details` tinyint(4) NOT NULL DEFAULT '0',
  `active` tinyint(4) NOT NULL DEFAULT '1',
  `xerId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `xecasehistory_caseId_IDX` (`caseId`),
  KEY `xecasehistory_typeskey` (`typesKey`),
  KEY `xecasehistory_active_govtId` (`active`,`govtId`),
  KEY `xecasehistory_type` (`type`),
  KEY `xercasehistory_entryDate` (`entryDate`),
  KEY `xercasehistory_fieldName` (`fieldName`),
  KEY `xercasehistory_recordTable` (`recordTable`),
  KEY `xerId` (`xerId`),
  KEY `govtId` (`govtId`)
) ENGINE=InnoDB AUTO_INCREMENT=29726001 DEFAULT CHARSET=latin1

Explain result for first query

id,     select_type,      table,              type, possible_keys, key,     key_len, ref,     rows,      Extra
'1',    'SIMPLE',        'xercasehistory',   'ref', 'xerId',      'xerId', '5',    'const', '1256458', 'Using where'

Explain result for second query

id,   select_type, table,            type,    possible_keys,                                                              key,     key_len,  ref,    rows,       Extra
'1',  'SIMPLE',    'xercasehistory', 'range', 'xecasehistory_typeskey,xecasehistory_type,xercasehistory_fieldName,xerId', 'xerId', '5',      NULL,   '1256458',  'Using index condition; Using where'

Best Answer

For the first query:

SELECT 
    *
FROM
    xercasehistory
WHERE
    xerId = 192             
ORDER BY 
    id DESC 
LIMIT 10;

you need an index on (xerId, id) - or just on (xerId) if the table is InnoDB. Four seconds is a very long time for a query that is so simple and returns no more than 10 rows. Add this index and try again. Query time should drop to a few milliseconds.

For the second query:

SELECT 
    *
FROM
    xercasehistory
WHERE
        xerId = 192 
    AND type = 'case'
    AND fieldName = 'statusCode'
    AND typesKey = 5            
ORDER BY 
    id DESC 
LIMIT 10 ;

the same index would be useful. If efficiency is not superb (a few milliseconds) for this query with the same index, you may have many (thousands) rows that match the index (xerId = 192) but not so many that match all 4 conditions, so a more appropriate index (on all 4 columns + the id) would be more efficient.

As for:

We can see an index in all fields used in where clause (xerId, type, type, typesKey) ...

No, there isn't a composite index. Theer are 4 indexes, on each column separately.

Why the (xerId) index is not used, at least for the first query is weird though. I would replace it with an index on (xerId, Id) and check the EXPLAIN output after that.

Related Question