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

Extended keys prevents ORDER BY ... LIMIT from working

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.30
    • 5.5.31
    • 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

          People

            igor Igor Babaev
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.