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
| Field | Original Value | New Value |
|---|---|---|
| Component/s | Optimizer [ 10200 ] | |
| Component/s | OTHER [ 10125 ] |
| Workflow | MariaDB v2 [ 53823 ] | MariaDB v3 [ 65081 ] |
| Assignee | Sergei Petrunia [ psergey ] | Igor Babaev [ igor ] |
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.
| Workflow | MariaDB v3 [ 65081 ] | MariaDB v4 [ 139695 ] |
| Fix Version/s | 10.0 [ 16000 ] |
| Assignee | Igor Babaev [ igor ] |
I've set it to minor because the scenario is not necessarily common (data types and all). Feel free to adjust it if needed.