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

Sub-optimal query plan is picked from range vs another range vs ref

    XMLWordPrintable

Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 11.6
    • Optimizer
    • None

    Description

      CREATE TABLE tbl (
        ...
       
        PRIMARY KEY (pk),
        KEY key1 (key1,pk)
      ) ENGINE=InnoDB;

      And two query plans:

      # The bad one:
      mysql> explain SELECT * FROM tbl1 WHERE key1 = const1 AND pk > const2A limit 1,1;
      +----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
      | id | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra       |
      +----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
      |  1 | SIMPLE      | tbl1  | ref  | PRIMARY,key1  | key1 | 9       | const | 24398 | Using where |
      +----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+

      # The good one one:
      mysql> explain SELECT * FROM tbl1 WHERE key1 = const1 AND pk > const2B limit 1,1;
      +----+-------------+-------+-------+----------------+------+---------+------+---------+-------------+
      | id | select_type | table | type  | possible_keys  | key  | key_len | ref  | rows    | Extra       |
      +----+-------------+-------+-------+----------------+------+---------+------+---------+-------------+
      |  1 | SIMPLE      | tbl1  | range | PRIMARY,key1   | key1 | 17      | NULL | 1052604 | Using where |
      +----+-------------+-------+-------+-------------- -+------+---------+------+---------+-------------+

      optimizer's choice between Good and Bad query plans depends on the values of const2A and const2B.

      Note that both query plans use the same index, but the Good plan uses more key
      parts. The point is that Good plan should always be used, because it scans a
      proper subset of rows that the Bad plan is scanning.

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            psergei Sergei Petrunia
            Votes:
            2 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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