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

    • Type: Task
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Fix Version/s: 10.2
    • Component/s: Optimizer
    • Labels:
      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

            Assignee:
            psergey Sergei Petrunia
            Reporter:
            psergey Sergei Petrunia
            Votes:
            2 Vote for this issue
            Watchers:
            7 Start watching this issue

              Dates

              Created:
              Updated: