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

        1. mdev-4340.sql
          495 kB
          Sergei Petrunia

        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.