MySQL Forums
Forum List  »  Optimizer & Parser

Compound index (pkey) not used when it should be
Posted by: Dan Parker
Date: November 29, 2017 01:39PM

I'm sure this issue must be covered in other posts, but I've tried searching several different ways and have come up snake-eyes, so my apologies if it is in fact an old subject.

I have a version 5.7.18-ndb-7.6.3-cluster-gpl installation containing a Db with the following table (column names are changed for simplicity)...

CREATE TABLE `MyTable` (
  `DATETIME_COL`  DATETIME          NOT NULL,
  `CHAR_COL`      CHAR(20)          NOT NULL,
  `TINYINT_COL`   TINYINT UNSIGNED  NOT NULL DEFAULT 0,
  `INT_COL`       INT UNSIGNED      NOT NULL DEFAULT 0,
  PRIMARY KEY (`DATETIME_COL`, `CHAR_COL`))
ENGINE = INNODB;

...that contains 9,587,731 rows with well distributed key values. After running ANALYZE TABLE for the above, EXPLAIN for the following query, which matches a row that is 1,038,376 rows from the beginning primary key-wise...

SELECT DATETIME_COL, CHAR_COL FROM MyTable
 WHERE (DATETIME_COL, CHAR_COL) >= ('2017-11-17 17:32:34', '201051000020')
 ORDER BY DATETIME_COL, CHAR_COL LIMIT 21;

...produces the following info (emphasis mine):

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: MyTable
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 65
          ref: NULL
         rows: 21
     filtered: 100.00
        Extra: Using where; Using index

Executing the query produces correct results, but takes over 5 seconds. Executing the same query but with key values at/near the beginning of the table runs within a few milliseconds. Clearly the PRIMARY key is not being used, and of course does not even appear as a possible_key, and a scan is being performed. My question is...why in the world would this be the case? Changing the comparison to just equality (=) and removing the ORDER BY and LIMIT clause causes the PRIMARY key to show up as a possible_key and to actually be used for the query, as does removing CHAR_COL and the corresponding value from the WHERE clause (with no other changes). But I can't understand why this would be the case. Why wouldn't the optimizer use the primary key for the original query where it is so obviously appropriate?

Options: ReplyQuote


Subject
Views
Written By
Posted
Compound index (pkey) not used when it should be
334
November 29, 2017 01:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.