Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-4935

Optimiser choosing full table scan (instead of index range scan) on query order by primary key with limit.

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Duplicate
    • Affects Version/s: 5.7.22-22, 5.7.23-23
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      Hi,

      I found a situation where I believe the optimiser is taking the wrong decision.  It is doing a full table scan instead of a range scan on an index (followed by a few PK lookup).  It looks like the behaviour is caused by an ORDER BY primary key with a LIMIT, and it looks like it depends on the cardinality of the field in the WHERE clause.

      The table definition is below.  The query is the following:

      {{SELECT * FROM _test_jfg WHERE i1a = @v ORDER BY id DESC LIMIT 10;
      }}

      CREATE TABLE _test_jfg (
       id  bigint unsigned NOT NULL AUTO_INCREMENT,
       i1c varchar(255) NOT NULL,
       f1  varchar(255) NOT NULL,
       i1a bigint unsigned NOT NULL,
       i1b varchar(64) NOT NULL,
       f2  int unsigned DEFAULT NULL,
       f3  varchar(255),
       f4  bigint unsigned DEFAULT NULL,
       f5  text NOT NULL,
       f6  int unsigned DEFAULT NULL,
       f7  tinyint NOT NULL,
       f8  text,
       i2b int unsigned NOT NULL,
       i2c datetime NOT NULL,
       f9  datetime NOT NULL,
       PRIMARY KEY (id),
       KEY i1 (i1a,i1b,i1c),
       KEY i2 (i1a,i2b,i2c),
       KEY i3 (i2c,i2b),
       KEY i4 (i1c(16),i1b(16))
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

      If the cardinality is high (more than 30.000 rows in the case below), the explain shows a full table scan:

      mysql> explain SELECT * FROM _test_jfg WHERE i1a = @v1 ORDER BY id DESC LIMIT 10\G
      *************************** 1. row ***************************
                 id: 1 select_type: SIMPLE
              table: _test_jfg
         partitions: NULL
               type: index
      possible_keys: i1,i2
                key: PRIMARY
            key_len: 8
                ref: NULL
               rows: 14974
           filtered: 0.07
              Extra: Using where
      1 row in set, 1 warning (0.00 sec)

      If the cardinality is low (1 in the case below), the explain shows using an index:

      {{mysql> explain SELECT * FROM _test_jfg WHERE i1a = @v1 ORDER BY id DESC LIMIT 10\G
      }}*************************** 1. row ***************************{{               id: 1
      }}{{  select_type: SIMPLE
      }}{{        table: _test_jfg
      }}{{   partitions: NULL
      }}{{         type: ref
      }}{{possible_keys: i1,i2
      }}{{          key: i1
      }}{{      key_len: 8
      }}{{          ref: const
      }}{{         rows: 1
      }}{{     filtered: 100.00
      }}{{        Extra: Using index condition; Using filesort
      }}1 row in set, 1 warning (0.00 sec)

      The table is quite big: many tenths of GB and 91M rows.

      To solve this problem, I am forcing index i2 (i1 is bigger than i2 because it includes 2 VARCHARs, so I am surprised it is used by explain above).

      The query is taking 12 minutes in the worse case, which is quite bad considering that the query forcing the index is talking less than 0.1 second.

      Note that the query will take a lot of time only if the table scan does not find rows quickly, the limit in the query allowing some full table scan to return quickly.  It however looks like a bad gamble, which is shown by the case where the query takes 12 minutes.

      Many thanks for looking into that,

      JFG

        Smart Checklist

          Attachments

          1. PS-1653.test
            5 kB
          2. PS-4935_mysql5723.test
            9 kB
          3. PS-4935.test
            14 kB

            Issue Links

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  jeanfrancois.gagne Jean-François Gagné
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  6 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: