Details
Minor
Description
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I've set it to minor because the scenario is not necessarily common (data types and all). Feel free to adjust it if needed.