Description:
Hi,
This issue mostly gives problems in joins where if optimizer selects table order with
group by table being second temporary/filesort is used without need.
mysql> explain select count(*) from a where i=j group by j order by j desc limit 1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 7 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
1 row in set (0.00 sec)
mysql> explain select count(*) from a where i=j group by i order by i desc limit 1;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | a | index | NULL | i | 5 | NULL | 7 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
These queries are equivalent but yet use different execution plans. For reference however
we detect i=j clause:
mysql> explain select * from a where j=5 and i=j;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | a | ref | i | i | 5 | const | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from a where i=5 and i=j;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | a | ref | i | i | 5 | const | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
How to repeat:
CREATE TABLE a (
i int(11) default NULL,
j int(11) default NULL,
KEY i (i)
) ;
INSERT INTO a VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
explain select count(*) from a where i=j group by j order by j desc limit 1;
