[MDEV-29970] after upgrading MariaDB 10.2 to 10.6 WHERE IN () doesn't use index for BIGINT Created: 2022-11-08 Updated: 2023-01-22 Resolved: 2023-01-22 |
|
| Status: | Closed |
| Project: | MariaDB Server |
| Component/s: | None |
| Affects Version/s: | 10.6 |
| Fix Version/s: | N/A |
| Type: | Bug | Priority: | Major |
| Reporter: | johny | Assignee: | Unassigned |
| Resolution: | Incomplete | Votes: | 0 |
| Labels: | None | ||
| Attachments: |
|
| Description |
|
A table with more than 650,000,000 rows does not use an index after updating MariaDB from 10.2 to 10.6
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)
Curiously, if I put the bigint literals in quotes it uses a better plan: (3.jpg)
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 |
| Comments |
| Comment by Daniel Black [ 2022-11-08 ] |
|
By basic example test on 10.6.12 resulted in a the use of the pid index. Can you obtain an optimizer trace and attach as text. Like MDEV-28531 I think that the large values beyond max int should be dropped and result in impossible where. Quoted numbers apply the rules for type conversion. |