|
The following simple test case can demonstrate the problem:
set optimizer_switch='rowid_filter=off';
|
create table t1 (a int, b int, key (b), key (a)) engine=innodb;
|
insert into t1
|
select (rand(1)*1000)/10, (rand(1001)*1000)/50 from seq_1_to_1000;
|
analyze table t1 ;
|
explain extended select * from t1 where a in (17,51,5) and b=2;
|
MariaDB [test]> explain extended select * from t1 where a in (17,51,5) and b=2;
|
+------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------------+
|
| 1 | SIMPLE | t1 | ref | b,a | b | 5 | const | 59 | 49.15 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+----------+-------------+
|
It can be seen from the output of the last command that the selectivity of the condition is shown as absurdly low (49.15%). In fact it is pretty high:
MariaDB [test]> explain extended select * from t1 where a in (17,51,5);
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
|
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 30 | 100.00 | Using index condition |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
|
It happens because after the commit 2acd81af73ac337658eb646ba2434f46a6dc8dc5 for MDEV-6003: "EITS: ref access, keypart2=const vs keypart2=expr - inconsistent filtered% value" the function table_cond_selectivity() started discounting the selectivity of the condition b=2 twice:
the first time as a range condition:
sel /= (double)table->quick_rows[key] / (double) table->stat_records();
|
the second time as the ref condition:
sel /= table->field[fldno]->cond_selectivity;
|
Setting optimizer_switch='rowid_filter=off' does not help.
Most probably the bug can be reproduced in 10.1,10.2,10.3 with corresponding settings.
|