Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.0.13, 10.1.0
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.
Attachments
Activity
If I create an index on K(col_varchar_1024_utf8) the optimizer chooses the slow plan with the default value of optimizer_use_condition_selectivity (=1).
I'll try to figure out in 10.2 why the slow plan is so slow with the help of ANALYZE.
I've set it to minor because the scenario is not necessarily common (data types and all). Feel free to adjust it if needed.