|
Test data dump is uploaded to ftp.askmonty.org/public/mdev7786.dump.gz .
It is completely artificial, nothing confidential about it, it's just too big to be attached to a JIRA issue.
After loading the dump, run
analyze table t1, t2, t3;
|
explain SELECT t1.f6, t1.f6, t1.f3, t1.f4, t1.f7, t1.f8, t1.f9, DATE_FORMAT( t2.f3, '%Y-%m-%d' ), t3.f5 FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 AND t2.f3 >= '2015-03-14' AND t2.f3 <= '2015-03-14' LEFT JOIN t3 ON t3.f3=MONTH(t2.f3) ORDER BY t2.f3 limit 300;
|
SELECT t1.f6, t1.f6, t1.f3, t1.f4, t1.f7, t1.f8, t1.f9, DATE_FORMAT( t2.f3, '%Y-%m-%d' ), t3.f5 FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 AND t2.f3 >= '2015-03-14' AND t2.f3 <= '2015-03-14' LEFT JOIN t3 ON t3.f3=MONTH(t2.f3) ORDER BY t2.f3 limit 300;
|
On my machine, results are as follows:
|
5.5.29
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 109591 | Using temporary; Using filesort |
|
| 1 | SIMPLE | t2 | range | idx1,idx2 | idx2 | 3 | NULL | 998 | Using where; Rowid-ordered scan; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | t3 | ref | idx3 | idx3 | 4 | func | 669 | Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan |
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------------------------------------------------------------------+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> SELECT t1.f6, t1.f6, t1.f3, t1.f4, t1.f7, t1.f8, t1.f9, DATE_FORMAT( t2.f3, '%Y-%m-%d' ), t3.f5 FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 AND t2.f3 >= '2015-03-14' AND t2.f3 <= '2015-03-14' LEFT JOIN t3 ON t3.f3=MONTH(t2.f3) ORDER BY t2.f3 limit 300;
|
...
|
300 rows in set (54.61 sec)
|
|
5.5.42
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+----------------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+----------------------------------------------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 110563 | Using temporary; Using filesort |
|
| 1 | SIMPLE | t2 | range | idx1,idx2 | idx2 | 3 | NULL | 998 | Range checked for each record (index map: 0x6); Rowid-ordered scan |
|
| 1 | SIMPLE | t3 | ref | idx3 | idx3 | 4 | func | 936 | Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan |
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+----------------------------------------------------------------------------------+
|
3 rows in set (0.00 sec)
|
|
MariaDB [test]> SELECT t1.f6, t1.f6, t1.f3, t1.f4, t1.f7, t1.f8, t1.f9, DATE_FORMAT( t2.f3, '%Y-%m-%d' ), t3.f5 FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 AND t2.f3 >= '2015-03-14' AND t2.f3 <= '2015-03-14' LEFT JOIN t3 ON t3.f3=MONTH(t2.f3) ORDER BY t2.f3 limit 300;
|
...
|
interrupted after 15 min of execution
|
So, I haven't got an instant execution on 5.5.29 (I wonder if query cache had something to do with it in the initial report); nevertheless, the difference between 5.5.29 and 5.5.42 is glaring obvious.
10.0.17 also has the problem. The plan looks slightly different there, but the query is still slow:
|
10.0.17
|
+------+-------------+-------+------+---------------+------+---------+------+---------+----------------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+---------+----------------------------------------------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 109733 | Using temporary; Using filesort |
|
| 1 | SIMPLE | t2 | ALL | idx1,idx2 | idx2 | 3 | NULL | 3305947 | Range checked for each record (index map: 0x6) |
|
| 1 | SIMPLE | t3 | ref | idx3 | idx3 | 4 | func | 719 | Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan |
|
+------+-------------+-------+------+---------------+------+---------+------+---------+----------------------------------------------------------------------------------+
|
Command line options to start the server (most options must be unnecessary, this set contains all non-default options from the attached vars, apart from InnoDB ones):
--join_cache_level=8 --key_buffer_size=262144 --max_heap_table_size=67108864 --max_long_data_size=67108864 --mrr_buffer_size=1048576 --myisam_sort_buffer_size=536870912 --optimizer_switch=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=on,mrr_cost_based=off,mrr_sort_keys=on,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 --query_cache_limit=32768 --query_cache_size=67108864 --query_cache_strip_comments=ON --read_buffer_size=8388608 --read_rnd_buffer_size=1048576 --sort_buffer_size=524288 --tmp_table_size=67108864 --character-set-server=utf8
|
|
|
using mdev7786.dump.gz and patch on MDEV-6735
MariaDB [test]> explain SELECT t1.f6, t1.f6, t1.f3, t1.f4, t1.f7, t1.f8, t1.f9, DATE_FORMAT( t2.f3, '%Y-%m-%d' ), t3.f5 FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 AND t2.f3 >= '2015-03-14' AND t2.f3 <= '2015-03-14' LEFT JOIN t3 ON t3.f3=MONTH(t2.f3) ORDER BY t2.f3 limit 300;
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+-------------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 109143 | Using temporary; Using filesort |
|
| 1 | SIMPLE | t2 | range | idx1,idx2 | idx2 | 3 | NULL | 998 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | t3 | ref | idx3 | idx3 | 4 | func | 674 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+-------------------------------------------------+
|
|
300 rows in set (1 min 50.42 sec)
|
with patch on 10.0 branch head
MariaDB [test]> explain SELECT t1.f6, t1.f6, t1.f3, t1.f4, t1.f7, t1.f8, t1.f9, DATE_FORMAT( t2.f3, '%Y-%m-%d' ), t3.f5 FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 AND t2.f3 >= '2015-03-14' AND t2.f3 <= '2015-03-14' LEFT JOIN t3 ON t3.f3=MONTH(t2.f3) ORDER BY t2.f3 limit 300;
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+-------------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 110563 | Using temporary; Using filesort |
|
| 1 | SIMPLE | t2 | range | idx1,idx2 | idx2 | 3 | NULL | 998 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | t3 | ref | idx3 | idx3 | 4 | func | 553 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+------+--------+-------------------------------------------------+
|
300 rows in set (1 min 15.81 sec)
|
so solved, pending a full mtr of course.
|
vars.5.5.29.txt