CODEWITHSUNDEEP
mysqlsqldatabase
KeepZero
KeepZero

Reputation: 907

Why Does MySQL not use an index?

Our MySQL(Percona Server) database have a big table cotains 10 millions rows, there are many slow query longger than 40 seconds like this:

  SELECT col1, Seller, col3, col4, Id, col5  
    FROM table1 
   WHERE Seller = 346761 
     AND col1 IN (2, 3, 4) 
     AND col3 = 1  
     AND col4 NOT IN (5,6,7)  
ORDER BY Id DESC 
   LIMIT 0, 20;

I had create index on Seller, and col1, col3, col4. These indexes are individual, not Multiple-Column Index (AKA covering index). And the Id is primary key.

EXPLAIN show that MySQL query this sql using Primary key as index, not index about Seller.

+----+-------------+------------------+-------+--------------------+---------+---------+------+------+-------------+
| id | select_type | table            | type  | possible_keys      | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------------+-------+--------------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | trans_audit_list | index | Seller,AuditStatus | PRIMARY | 8       | NULL | 1483 | Using where | 
+----+-------------+------------------+-------+--------------------+---------+---------+------+------+-------------+

When I force index (Seller), it's very fast, 0.7 second.

And I found if not use limit statement, this query will use Seller index, will be very fast. Why MySQL not using index on Seller with limit statement?

Upvotes: 2

Views: 190

Answers (3)

user2151805
user2151805

Reputation:

Your non-cluster index on [Seller] could have too much fragmentation, and your statistics could be out of date, then the query optimizer may not select best query plan. Check fragmentation, update statistics, or re-build your non-cluster index on Seller, certainly if you can also create covering index just for this query, if this query is frequently used by user, and it worth the cost. When you create covering index, make sure the order is what end users mostly used. Like in your query, the covering index should be on [seller->col1->col3->col4]

Upvotes: 1

Sebas
Sebas

Reputation: 21542

Multiple possible indices situations added to an ORDER BY DESC + LIMIT clauses is a common problematic situation for mysql's optimizer and one of the very little cases when forcing an index could actually be legitimate.

Please note also that your clause IN (...) is an additional hassle for the optimizer.

If you don't really need it I suggest to order by id ASC instead of DESC so the engine could use the benefits of the primary key ordering, which is ASC until further implementation.

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332771

As you've encountered, having an index does not mean it will be used. That goes for every database -- index selection is up to the query optimizer. As you've tried, forcing an index does not mean you'll get the fastest result.

Following that, query caching might not be helping you -- the query plan could be cached so the index isn't even considered.

MySQL only uses one index per statement, so a covering index (multi-column) would be a good idea but you'll have to test it to be sure.

Upvotes: 1

Related Questions