Details
Description
When using IN() operator, if the provided values are both within the 1st half and the 2nd half of the column size ( < 9223372036854775808 and >= 9223372036854775808 for unsigned bigint), the index is not used.
This behavior has changed from the 10.1.* versions where the index was correctly used.
SQL to reproduce:
CREATE TABLE `a` ( |
`id` bigint AUTO_INCREMENT PRIMARY KEY, |
`c1` bigint unsigned, |
KEY (`c1`) |
) ENGINE=InnoDB;
|
|
|
INSERT INTO `a` VALUES (1,9223382399205928659),(2,9223384207280813348),(3,9223385953115437234),(4,9223387250780556749),(5,9223387354282558788),(6,9223387603870501596),(7,9223389270813433667),(8,9223389903231468827),(9,9223390280789586779),(10,9223391591398222899),(11,9223391875473564350),(12,9223393152250049433),(13,9223393939696790223),(14,9223394417225350415),(15,9223397646397141015),(16,9223398025879291243),(17,9223399038671098072),(18,9223399534968874556),(19,9223400449518009285),(20,9223400860292643549),(21,9223400940692256924),(22,9223401073791948119),(23,9223402820804649616),(24,9223403470951992681),(25,9223405581879567267),(26,9223405754978563829),(27,9223405972966828221); |
|
|
MariaDB [test]> explain SELECT c1 FROM a WHERE c1 IN ( 1, 9223372036854775807 ); |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ |
| 1 | SIMPLE | a | range | c1 | c1 | 9 | NULL | 2 | Using where; Using index | |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ |
|
|
|
|
MariaDB [test]> explain SELECT c1 FROM a WHERE c1 IN ( 1, 9223372036854775808 ); |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ |
| 1 | SIMPLE | a | index | c1 | c1 | 9 | NULL | 27 | Using where; Using index | |
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ |
Attachments
Issue Links
- relates to
-
MDEV-18319 BIGINT UNSIGNED Performance issue
-
- Closed
-
-
-
- Closed
-