Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.5.12, 10.6.8
-
None
-
Debian 11
Description
I upgraded from Debian 10 to Debian 11. Debian 10 had mariadb 10.3.31 and Debian 11 has mariadb 10.5.12.
Since the upgrade, queries with IN statement that include single quoted numbers (making them text instead of int) have very lower performance.
I attach two example queries, one with quotes, one without. I will write them here cut down, with the explain output for both mariadb 10.3.31 and 10.5.12
10.3.31 with quotes
explain SELECT * FROM video
WHERE id in ('1283','1284','1298', [ about 14000 ids deleted] , '105149','105152','105153')
LIMIT 0, 14;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE video range PRIMARY PRIMARY 4 NULL 14488 Using where
takes 0.05 sec
10.3.31 without quotes
explain SELECT * FROM video
WHERE id in (1283,1284,1298, [ about 14000 ids deleted] , 105149,105152,105153)
LIMIT 0, 14;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY video ALL PRIMARY NULL NULL NULL 14898
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 14488
3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
takes about 0.01 sec
10.5.12 with quotes
explain SELECT * FROM video
WHERE id in ('1283','1284','1298', [ about 14000 ids deleted] , '105149','105152','105153')
LIMIT 0, 14;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE video ALL PRIMARY NULL NULL NULL 14889 Using where
Takes about 2,3 seconds
10.5.12 without quotes
explain SELECT * FROM video
WHERE id in (1283,1284,1298, [ about 14000 ids deleted] , 105149,105152,105153)
LIMIT 0, 14;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY video ALL PRIMARY NULL NULL NULL 14889
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 14488
3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
takes 0.01 sec
So the first problem is the different optimization for the query with the quotes, resulting in 2,3 seconds execution instead of 0,05 seconds in mariadb 10.3.31. 10.3.31 uses range while 10.5.12 does not. Second problem is that mariadb is not doing IN predicate for single quoted integers, although I thought that MDEV-21265 solved this (apparently it didn't).
Attachments
Issue Links
- is duplicated by
-
MDEV-27380 After minor upgrade to 10.5.13 queries with large IN clauses have performance issues
- Closed