|
ORDER BY limit optimizer doesn't take condition selectivity into account
Test dataset:
create table ten(a int);
|
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
create table one_k(a int);
|
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
|
|
create table t10 (
|
key1 int,
|
col1 int,
|
filler1 char(32),
|
key(key1)
|
);
|
|
insert into t10 select
|
A.a + 1000*B.a,
|
MOD(A.a + 1000*B.a, 10000),
|
'filler-data'
|
from
|
one_k A, one_k B;
|
|
set use_stat_tables=preferably;
|
set histogram_size=100;
|
set optimizer_use_condition_selectivity=4;
|
analyze table t10 persistent for all;
|
Then, run:
explain extended select * from t10 where col1=1000111 order by key1 desc limit 10 ;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+-------------+
|
| 1 | SIMPLE | t10 | index | NULL | key1 | 5 | NULL | 10 | 100.00 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+-------------+
|
It's going to scan the entire index. Note that filtered=100%, even if "col1=1000111" is very selective.
Let's check that other parts of the optimizer are aware of this:
MariaDB [j12]> explain extended select * from t10 where col1=1000111;
|
+------+-------------+-------+------+---------------+------+---------+------+---------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+---------+----------+-------------+
|
| 1 | SIMPLE | t10 | ALL | NULL | NULL | NULL | NULL | 1000000 | 0.00 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+---------+----------+-------------+
|
|