Re: Query with GROUP BY (Using index for group-by -> Using index)

[mnf]

mysql> EXPLAIN SELECT i, n, t, v FROM shn1 AS outer WHERE i = (SELECT MAX(i) FROM shn1 WHERE n=outer.n);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer WHERE i = (SELECT MAX(i) FROM shn1 WHERE n=outer.n)' at line 1

If you mean this:
mysql> EXPLAIN SELECT i, n, t, v FROM shn1 AS T1 WHERE i = (SELECT MAX(i) FROM shn1 WHERE n = T1.n);
+----+--------------------+-------+------+---------------+------+---------+---------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+---------+---------+-------------+
| 1 | PRIMARY | T1 | ALL | NULL | NULL | NULL | NULL | 7699429 | Using where |
| 2 | DEPENDENT SUBQUERY | shn1 | ref | n,ni,nt | ni | 2 | br.T1.n | 35811 | Using index |
+----+--------------------+-------+------+---------------+------+---------+---------+---------+-------------+

then it is full scan again, takes hours, and obviously is not "SELECT MAX(i) FROM shn1 GROUP BY n".