|
Prepare the dataset:
create table ten(a int);
|
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
create table t1 (
|
kp1 int, kp2 int,
|
filler1 char(100),
|
filler2 char(100),
|
key(kp1, kp2)
|
);
|
|
insert into t1
|
select
|
A.a,
|
B.a,
|
'filler-data-1',
|
'filler-data-2'
|
from ten A, ten B, ten C;
|
set histogram_size=100;
|
set use_stat_tables='preferably';
|
set optimizer_use_condition_selectivity=4;
|
analyze table t1 persistent for all;
|
Now, let's try a ref access. Let's start without ref(const):
explain extended select * from ten, t1 where t1.kp1=ten.a and t1.kp2=ten.a+1;
|
+------+-------------+-------+------+---------------+------+---------+----------------+------+----------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+----------------+------+----------+-----------------------+
|
| 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
|
| 1 | SIMPLE | t1 | ref | kp1 | kp1 | 10 | j19.ten.a,func | 10 | 100.00 | Using index condition |
|
+------+-------------+-------+------+---------------+------+---------+----------------+------+----------+-----------------------+
|
So, ref access will give us 10 rows (on every index lookup). Ok.
explain extended select * from ten, t1 where t1.kp1=ten.a and t1.kp2=4;
|
+------+-------------+-------+------+---------------+------+---------+-----------------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-----------------+------+----------+-------------+
|
| 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
|
| 1 | SIMPLE | t1 | ref | kp1 | kp1 | 10 | j19.ten.a,const | 10 | 9.90 | |
|
+------+-------------+-------+------+---------------+------+---------+-----------------+------+----------+-------------+
|
This one seems to be wrong. ref access still produces 10 rows, but then filtered=9.90% , which is what selectivity would be if we weren't using ref access.
Indeed, if we disable ref access:
explain extended select * from ten, t1 ignore index(kp1) where t1.kp1=ten.a and t1.kp2=4;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
|
| 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | |
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | 9.90 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
|
we get selectivity=9.90%.
|