5

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'
  • And please replace the desc xercasehistory with the output of SHOW CREATE TABLE xercasehistory;. It's far more informative. – ypercubeᵀᴹ Feb 10 '17 at 10:00
  • ok. In a minute – Rahul Feb 10 '17 at 10:02
  • Thnx. I edited and formatted the output. Can you also add the output of EXPLAIN SELECT ...; for both queies? It's weird that the (xerID) index is not used, at least for the first query. Also ad which version of mysql you use. – ypercubeᵀᴹ Feb 10 '17 at 10:07
  • Alright. doing that. – Rahul Feb 10 '17 at 10:10
4

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.

  • so, you are asking me to add a compound index. There are two questions. 1. Table already has index in all fields used in where conditions so I was expecting that to work more faster because first, it should work on xerId=192 and then rest of the conditions has to work on filtered data. Am I wrong here ? 2. Why you want to add id at the end of compound index ? Kindly explain or help me understand or any link will help. Thanks in advance – Rahul Feb 10 '17 at 10:09
  • The (xerID, ID) is useful for the first query because you have ORDER BY id and LIMIT 10. The second part of the index (id) wil lbe used to do this sort and limit more efficiently. Now in theory, the (xerID) index in an InnoDB table should be able to do that anyway - as it includes the PK / id by default so I don't know exactly what's happening there - that's why I'm asking for the version. – ypercubeᵀᴹ Feb 10 '17 at 10:11
  • The compound index on (xerId, type, type, typesKey) will be more efficient for the 2nd query that 4 separate indexes. – ypercubeᵀᴹ Feb 10 '17 at 10:13
  • ran select version() and got the result 5.6.19-log – Rahul Feb 10 '17 at 10:17
  • edited the question and added explain result for first query. – Rahul Feb 10 '17 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.