|
Without EITS/optimizer_use_codition_selectivity, the query in the test case below sometimes is executed with eq_ref, and sometimes with ref. But with EITS/optimizer_use_codition_selectivity>=3, it seems always ref.
Execution with ref takes approximately 5 times longer.
ANALYZE TABLE L, K, J;
|
+--------+---------+----------+-----------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+--------+---------+----------+-----------------------------+
|
| test.L | analyze | status | OK |
|
| test.K | analyze | status | Table is already up to date |
|
| test.J | analyze | status | OK |
|
+--------+---------+----------+-----------------------------+
|
EXPLAIN EXTENDED SELECT alias1 . `col_varchar_10_latin1` AS field1 , alias2 . `col_varchar_1024_latin1_key` AS field2 FROM L AS alias1 LEFT JOIN K AS alias2 LEFT JOIN J AS alias3 ON alias2 . `col_varchar_10_latin1_key` = alias3 . `col_varchar_1024_utf8_key` ON alias1 . `col_int` = alias3 . `pk` WHERE alias3 . `col_varchar_1024_utf8` IS NOT NULL AND alias2 . `col_varchar_1024_utf8` > 'v' AND alias2 . `col_varchar_1024_utf8` < 'z';
|
+------+-------------+--------+--------+-----------------------------------+---------+---------+---------------------+------+----------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+--------+--------+-----------------------------------+---------+---------+---------------------+------+----------+-------------------------------------------------+
|
| 1 | SIMPLE | alias1 | ALL | NULL | NULL | NULL | NULL | 2927 | 100.00 | Using where |
|
| 1 | SIMPLE | alias3 | eq_ref | PRIMARY,col_varchar_1024_utf8_key | PRIMARY | 4 | test.alias1.col_int | 1 | 100.00 | Using where |
|
| 1 | SIMPLE | alias2 | ALL | NULL | NULL | NULL | NULL | 3000 | 100.00 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+--------+--------+-----------------------------------+---------+---------+---------------------+------+----------+-------------------------------------------------+
|
SELECT alias1 . `col_varchar_10_latin1` AS field1 , alias2 . `col_varchar_1024_latin1_key` AS field2 FROM L AS alias1 LEFT JOIN K AS alias2 LEFT JOIN J AS alias3 ON alias2 . `col_varchar_10_latin1_key` = alias3 . `col_varchar_1024_utf8_key` ON alias1 . `col_int` = alias3 . `pk` WHERE alias3 . `col_varchar_1024_utf8` IS NOT NULL AND alias2 . `col_varchar_1024_utf8` > 'v' AND alias2 . `col_varchar_1024_utf8` < 'z';
|
+------------+----------
|
| field1 | field2
|
+------------+----------
|
...
|
+------------+----------
|
1034 rows in set (0.11 sec)
|
SET use_stat_tables = PREFERABLY, optimizer_use_condition_selectivity = 3;
|
ANALYZE TABLE L, K, J;
|
+--------+---------+----------+-----------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+--------+---------+----------+-----------------------------------------+
|
| test.L | analyze | status | Engine-independent statistics collected |
|
| test.L | analyze | status | OK |
|
| test.K | analyze | status | Engine-independent statistics collected |
|
| test.K | analyze | status | Table is already up to date |
|
| test.J | analyze | status | Engine-independent statistics collected |
|
| test.J | analyze | status | OK |
|
+--------+---------+----------+-----------------------------------------+
|
EXPLAIN EXTENDED SELECT alias1 . `col_varchar_10_latin1` AS field1 , alias2 . `col_varchar_1024_latin1_key` AS field2 FROM L AS alias1 LEFT JOIN K AS alias2 LEFT JOIN J AS alias3 ON alias2 . `col_varchar_10_latin1_key` = alias3 . `col_varchar_1024_utf8_key` ON alias1 . `col_int` = alias3 . `pk` WHERE alias3 . `col_varchar_1024_utf8` IS NOT NULL AND alias2 . `col_varchar_1024_utf8` > 'v' AND alias2 . `col_varchar_1024_utf8` < 'z';
|
+------+-------------+--------+------+-----------------------------------+---------------------------+---------+------+------+----------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+--------+------+-----------------------------------+---------------------------+---------+------+------+----------+-------------------------------------------------+
|
| 1 | SIMPLE | alias2 | ALL | NULL | NULL | NULL | NULL | 3000 | 16.00 | Using where |
|
| 1 | SIMPLE | alias3 | ref | PRIMARY,col_varchar_1024_utf8_key | col_varchar_1024_utf8_key | 768 | func | 1 | 100.00 | Using where |
|
| 1 | SIMPLE | alias1 | ALL | NULL | NULL | NULL | NULL | 3000 | 100.00 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+--------+------+-----------------------------------+---------------------------+---------+------+------+----------+-------------------------------------------------+
|
SELECT alias1 . `col_varchar_10_latin1` AS field1 , alias2 . `col_varchar_1024_latin1_key` AS field2 FROM L AS alias1 LEFT JOIN K AS alias2 LEFT JOIN J AS alias3 ON alias2 . `col_varchar_10_latin1_key` = alias3 . `col_varchar_1024_utf8_key` ON alias1 . `col_int` = alias3 . `pk` WHERE alias3 . `col_varchar_1024_utf8` IS NOT NULL AND alias2 . `col_varchar_1024_utf8` > 'v' AND alias2 . `col_varchar_1024_utf8` < 'z';
|
+------------+----------
|
| field1 | field2
|
+------------+----------
|
...
|
+------------+----------
|
1034 rows in set (0.66 sec)
|
Execution times above are from 10.1 commit d161546b67142cdd5322a4ed160441045ae0cd1e. Also reproducible on 10.0 and on debug builds – execution time is different there, but the approximate ratio holds.
The data dump is attached.
|
dump.gz