[#MDEV-337] A query with simple join, where clause and order by <datetime column> desc takes very long when executed with ICP

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;

[MDEV-337] A query with simple join, where clause and order by <datetime column> desc takes very long when executed with ICP Created: 2012-06-13 Updated: 2012-06-28 Resolved: 2012-06-16
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	5.5.24
Fix Version/s:	5.5.25

[MDEV-337] A query with simple join, where clause and order by <datetime column> desc takes very long when executed with ICP Created: 2012-06-13 Updated: 2012-06-28 Resolved: 2012-06-16