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).
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
Running with optimizer_switch='extended_keys=on' may cause ORDER BY .. LIMIT optimizer to skip efficient query plans. Cnsider the following queries: {noformat} 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 | | +------+-------------+----------+--------+--------------------+----------------+---------+-------------------------+------+-------------+ {noformat} {noformat} 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) {noformat} Here, index page_timestamp is defined as {noformat} KEY `page_timestamp` (`rev_page`,`rev_timestamp`) {noformat} |
Running with optimizer_switch='extended_keys=on' may cause ORDER BY .. LIMIT optimizer to skip efficient query plans. Cnsider the following query: {noformat} 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 | | +------+-------------+----------+--------+--------------------+----------------+---------+-------------------------+------+-------------+ {noformat} Here, index page_timestamp is defined as {noformat} KEY `page_timestamp` (`rev_page`,`rev_timestamp`) {noformat} 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: {noformat} 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) {noformat} Use of filesort may cause performance degradation. (the testcase for this bug is too small to show it, though). |
| Attachment | mdev-4340.sql [ 21515 ] |
| Priority | Critical [ 2 ] | Major [ 3 ] |
The fix for this bug was pushed into the 5.5 tree (rev 3737).
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Workflow | defaullt [ 26827 ] | MariaDB v2 [ 46539 ] |
| Workflow | MariaDB v2 [ 46539 ] | MariaDB v3 [ 67245 ] |
| Workflow | MariaDB v3 [ 67245 ] | MariaDB v4 [ 146566 ] |
Test dataset