Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.30
-
None
-
None
Description
Running with optimizer_switch='extended_keys=on' may cause ORDER BY .. LIMIT optimizer to skip efficient query plans.
Cnsider the following query:
MariaDB [mw]> explain SELECT /* WikiExporter::dumpFrom */ * FROM `page` INNER JOIN `revision` IGNORE INDEX (PRIMARY) ON ((page_id=rev_page)) INNER JOIN `text` ON ((rev_text_id=old_id)) WHERE page_namespace=4 AND page_title='Sandbox' ORDER BY rev_timestamp ASC LIMIT 10;
|
+------+-------------+----------+--------+--------------------+----------------+---------+-------------------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------+--------+--------------------+----------------+---------+-------------------------+------+-------------+
|
| 1 | SIMPLE | page | const | PRIMARY,name_title | name_title | 261 | const,const | 1 | |
|
| 1 | SIMPLE | revision | ref | page_timestamp | page_timestamp | 4 | const | 10 | Using where |
|
| 1 | SIMPLE | text | eq_ref | PRIMARY | PRIMARY | 4 | mw.revision.rev_text_id | 1 | |
|
+------+-------------+----------+--------+--------------------+----------------+---------+-------------------------+------+-------------+
|
Here, index page_timestamp is defined as
KEY `page_timestamp` (`rev_page`,`rev_timestamp`)
|
The ref_page part is constant due to use of ref(const) access. Hence, the optimizer is able to conclude that ordering required by "ORDER BY rev_timestamp" is produced by the used index.
If one sets extended_keys=on, the query plan remains the same, except that filesort is used:
MariaDB [mw]> explain SELECT /* WikiExporter::dumpFrom */ * FROM `page` INNER JOIN `revision` IGNORE INDEX (PRIMARY) ON ((page_id=rev_page)) INNER JOIN `text` ON ((rev_text_id=old_id)) WHERE page_namespace=4 AND page_title='Sandbox' ORDER BY rev_timestamp ASC LIMIT 10;
|
+------+-------------+----------+--------+--------------------+----------------+---------+-------------------------+------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------+--------+--------------------+----------------+---------+-------------------------+------+----------------------------------------------+
|
| 1 | SIMPLE | page | const | PRIMARY,name_title | name_title | 261 | const,const | 1 | Using where; Using temporary; Using filesort |
|
| 1 | SIMPLE | revision | ref | page_timestamp | page_timestamp | 4 | mw.page.page_id | 1 | |
|
| 1 | SIMPLE | text | eq_ref | PRIMARY | PRIMARY | 4 | mw.revision.rev_text_id | 1 | |
|
+------+-------------+----------+--------+--------------------+----------------+---------+-------------------------+------+----------------------------------------------+
|
3 rows in set (0.00 sec)
|
Use of filesort may cause performance degradation. (the testcase for this bug is too small to show it, though).