Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
MariaDB 10.x.y (unlike MySQL 5.7) does not do proper optimization for queries having row value expressions in the IN clause.
Check this simple case:
create table b (
|
b_id bigint auto_increment not null,
|
d date,
|
primary key (b_id, d));
|
|
|
insert into b(d) values ( date_add('2016-07-13', INTERVAL -floor(rand()*5) DAY));
|
insert into b(d) values ( date_add('2016-07-13', INTERVAL -floor(rand()*5) DAY));
|
replace into b(d) select date_add('2016-07-13', INTERVAL -floor(rand()*5) DAY) from b t1, b t2, b t3, b t4, b t5, b t6, b t7, b t8, b t9, b t10, b t11, b t12, b t13, b t14, b t15, b t16, b t17, b t18;
|
Now, compare the plans for the following equivalent queries:
MariaDB [test]> explain SELECT *
|
-> FROM b
|
-> WHERE (b_id, d) IN
|
-> ((1, date('2016-07-09')),
|
-> (2, date('2016-07-09')),
|
-> (3, date('2016-07-09')))\G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: b
|
type: index
|
possible_keys: NULL
|
key: PRIMARY
|
key_len: 11
|
ref: NULL
|
rows: 262146
|
Extra: Using where; Using index
|
1 row in set (0.02 sec)
|
|
|
MariaDB [test]> explain SELECT * FROM b WHERE ((b_id = 1 ) and (d = date('2016-07-09'))) or ((b_id = 2) and (d = date('2016-07-09'))) or ((b_id = 3) and (d = date('2016-07-09')))\G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: b
|
type: range
|
possible_keys: PRIMARY
|
key: PRIMARY
|
key_len: 11
|
ref: NULL
|
rows: 3
|
Extra: Using where; Using index
|
1 row in set (0.02 sec)
|
MySQL 5.7.x optimize the original query in the same way as the latter rewritten one above, and they make a big deal out of it, see http://downloads.mysql.com/presentations/innovation-day-2016/Session_8_Performance_Improvements_in_MySQL_Optimizer.pdf (slides 14-16)