Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.6
-
None
-
None
Description
A table with more than 650,000,000 rows does not use an index after updating MariaDB from 10.2 to 10.6
CREATE TABLE `parts` (
|
`mainid` int(10) UNSIGNED NOT NULL, |
`pid` bigint(20) UNSIGNED NOT NULL, |
`name` varchar(40) COLLATE utf8mb3_unicode_ci NOT NULL, |
`dt` date NOT NULL
|
)
|
 |
ALTER TABLE `parts`
|
ADD PRIMARY KEY (`mainid`),
|
ADD KEY `pid` (`pid`);
|
Maria uses an index when my queries have WHERE pid IN ( n1 ), but it doesn't use an index when my queries have WHERE pid IN ( n1, n2, n3 ).
Same query in MariaDB 10.2 (1.jpg) and MariaDB 10.6 (2.jpg)
EXPLAIN
|
SELECT
|
*
|
FROM
|
parts
|
WHERE
|
pid IN (
|
6826931823469672941, |
2750571406036799415, |
156705013816687978 |
);
|
Curiously, if I put the bigint literals in quotes it uses a better plan: (3.jpg)
EXPLAIN
|
SELECT
|
*
|
FROM
|
parts
|
WHERE
|
pid IN (
|
'6826931823469672941', |
'2750571406036799415', |
'156705013816687978' |
);
|
I tried to change eq_range_index_dive_limit to 0 (value in 10.2) from 200 (value in 10.6) in my.cnf, but it didn't help. Same with the maximum value of 4294967295.
Can I use single quotes for BIGINT? As I understand it, it's the same if I don't use them