Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.24
-
None
-
None
Description
The following query
SELECT * FROM t1, t2
|
WHERE c = i AND a = 1000 AND e = 'cc' AND h = 152 AND g = 'value22.test' AND f > '2012-06-15 20:42:05'
|
ORDER BY f DESC;
|
on the test data (100K rows in t1, 2 rows in t2) takes several seconds on a release build and over a minute on a debug build (and returns an empty set). The same query without DESC, or any of the conditions in WHERE clause finishes in no time.
The test data is attached as mdev-337.txt.gz
Reproducible on 5.5.23, 5.5.24 and current maria/5.5 revno 3426.
In the test data, the first table is Aria. With MyISAM table, you can notice some slowness too, but it's much less perceptible.
Due to this, it's difficult to be certain whether the problem exists on mysql-trunk.
Could not reproduce on maria-5.3.
Minimal optimizer_switch:
index_condition_pushdown=on
Full optimizer_switch (default):
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off
|
EXPLAIN (with the minimal optimizer_switch):
EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE c = i AND a = 1000 AND e = 'cc' AND h = 152 AND g = 'value22.test' AND f > '2012-06-15 20:42:05' ORDER BY f desc;
|
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+--------------------------+
|
| 1 | SIMPLE | t1 | range | aehgfcd | aehgfcd | 40 | NULL | 1 | 100.00 | Using index condition |
|
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.c | 1 | 100.00 | Using where; Using index |
|
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+--------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t1`.`e` AS `e`,`test`.`t1`.`f` AS `f`,`test`.`t1`.`g` AS `g`,`test`.`t1`.`h` AS `h`,`test`.`t2`.`i` AS `i` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`g` = 'value22.test') and (`test`.`t1`.`h` = 152) and (`test`.`t1`.`e` = 'cc') and (`test`.`t1`.`a` = 1000) and (`test`.`t1`.`c` = `test`.`t2`.`i`) and (`test`.`t1`.`f` > '2012-06-15 20:42:05')) order by `test`.`t1`.`f` desc |
|
Test case:
- unpack the attached file mdev-337.txt.gz and feed it to the server;
|
- execute
|
SELECT * FROM t1, t2
|
WHERE c = i AND a = 1000 AND e = 'cc' AND h = 152 AND g = 'value22.test' AND f > '2012-06-15 20:42:05'
|
ORDER BY f DESC;
|