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?