Mysql Query takes forever after adding a where condition

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

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.

StackExchange Database Administrators Q#163855, answer score: 4