Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Order by with and without Limit clause uses different index

Can someone explain why the optimizer chooses different indexes if Limit is used?

MariaDB >

EXPLAIN SELECT dms_meta.docid 
FROM dms_meta 
WHERE (dms_meta.metid = 3 and dms_meta.value = '2015-10-01') 
ORDER BY dms_meta.docid ASC limit 25;

+------+-------------+----------+-------+---------------+------------+---------+------+------+-------------+
| id   | select_type | table    | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+------+-------------+----------+-------+---------------+------------+---------+------+------+-------------+
|    1 | SIMPLE      | dms_meta | index | indx_metaid   | indx_docid | 8       | NULL | 889  | Using where |
+------+-------------+----------+-------+---------------+------------+---------+------+------+-------------+

1 row in set (0.001 sec)

MariaDB >

EXPLAIN SELECT dms_meta.docid 
FROM dms_meta 
WHERE (dms_meta.metid = 3 and dms_meta.value = '2015-10-01') 
ORDER BY dms_meta.docid ASC;

+------+-------------+----------+------+---------------+-------------+---------+-------+--------+-----------------------------+
| id   | select_type | table    | type | possible_keys | key         | key_len | ref   | rows   | Extra                       |
+------+-------------+----------+------+---------------+-------------+---------+-------+--------+-----------------------------+
|    1 | SIMPLE      | dms_meta | ref  | indx_metaid   | indx_metaid | 4       | const | 121880 | Using where; Using filesort |
+------+-------------+----------+------+---------------+-------------+---------+-------+--------+-----------------------------+

1 row in set (0.000 sec)

The first query completes in 0.5 second, whereas the second one in more than 6 seconds. I am using
mysql Ver 15.1 Distrib 10.11.4-MariaDB

Oddly enough on on MySql 5.0.95 both queries choose the same index and complete in less than a second.

Any suggections?

Selecting different indexes with Limit in select makses huge impact on performance, but I need the engine to make the proper selection everytime

Tags:

2

Answers


  1. 0

    No, I can’t completely explain the choice. But here is some hand-waving. Statistics are gathered on the selectivity of the columns mentioned in the possible indexes and the "best" one is picked. It may not be the best when the query actually runs.

    Your query would run fun much faster with

    INDEX(metid, value, docid)

    If value is TEXT, then Ugh! This smells like an Entity-Attribute-Value schema, which has all sorts of problems. For that, here is what I recommend: Entity-Attribute-Value and here is a plugin that helps WordPress: WP Index Improvements

    Login or Signup to reply.
  2. 0

    When you use LIMIT 25, the best performance can be achieved by iterating through the index for the ORDER BY column, testing the WHERE condition on each row and stopping when 25 matches are found.

    Without the limit, it can do better by using the index that filters on one of the columns in the WHERE clause. This reduces the total number of rows that have to be sorted by ORDER BY after everything is fetched.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search