Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.2.6
-
Ubuntu 16.04 x64
Description
The following appears to be a query with inner join and sort running ~8.5x faster when the query planner opts not to use an index. The tables are both freshly optimized and analyzed.
[mydb]>analyze select * from tbl2, tbl1 force index (primary) where tbl2.varchar_col_2='someval' and tbl1.varchar_col_2='someval' and tbl2.varchar_col1=tbl1.varchar_col1 and tbl1.char_col_1='Y' and tbl1.int_col_2 < 58 and tbl1.date_col1 < '2004-01-01' order by tbl1.datetime_col1, tbl1.int_col_1; |
+------+-------------+--------+--------+--------------------------------------+---------+---------+-----------------------+----------+--------+----------+------------+-----------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | |
+------+-------------+--------+--------+--------------------------------------+---------+---------+-----------------------+----------+--------+----------+------------+-----------------------------+ |
| 1 | SIMPLE | tbl1 | ALL | NULL | NULL | NULL | NULL | 10116130 | 2.00 | 23.86 | 100.00 | Using where; Using filesort | |
| 1 | SIMPLE | tbl2 | eq_ref | PRIMARY,tbl2_varchar_col_2 | PRIMARY | 32 | mydb.tbl1.varchar_col1 | 1 | 1.00 | 97.67 | 100.00 | Using where | |
+------+-------------+--------+--------+--------------------------------------+---------+---------+-----------------------+----------+--------+----------+------------+-----------------------------+ |
2 rows in set (6.08 sec) |
[mydb]>analyze select * from tbl2, tbl1 where tbl2.varchar_col_2='someval' and tbl1.varchar_col_2='someval' and tbl2.varchar_col1=tbl1.varchar_col1 and tbl1.char_col_1='Y' and tbl1.int_col_2 < 58 and tbl1.date_col1 < '2004-01-01' order by tbl1.datetime_col1, tbl1.int_col_1; |
+------+-------------+--------+------+------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+--------------+-------------------------+--------+-----------+----------+------------+----------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | |
+------+-------------+--------+------+------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+--------------+-------------------------+--------+-----------+----------+------------+----------------------------------------------+ |
| 1 | SIMPLE | tbl2 | ref | PRIMARY,tbl2_varchar_col_2 | tbl2_varchar_col_2 | 32 | const | 892048 | 870220.00 | 100.00 | 100.00 | Using where; Using temporary; Using filesort | |
| 1 | SIMPLE | tbl1 | ref | tbl1_varchar_col1,tbl1_date_col1,tbl1_varchar_col_2,tbl1_char_col_1,tbl1_varchar_col1_datetime_col1_varchar_col_2_char_col_1 | tbl1_varchar_col1_datetime_col1_varchar_col_2_char_col_1 | 102 | mydb.tbl2.varchar_col1 | 1 | 6.41 | 28.12 | 0.00 | Using where | |
+------+-------------+--------+------+------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+--------------+-------------------------+--------+-----------+----------+------------+----------------------------------------------+ |
2 rows in set (50.94 sec) |