Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4340

Extended keys prevents ORDER BY ... LIMIT from working

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5.30
    • Fix Version/s: 5.5.31
    • Component/s: None
    • Labels:
      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

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              psergey Sergei Petrunia
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: