mysql> explain select id
-> from file
-> where size in
-> (
-> select size from
-> (
-> select count(*) as c, size
-> from file
-> group by size
-> having c>1 and size>0
-> ) as t
-> );
+----+--------------------+------------+-------+---------------+----------+-----
----+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_
len | ref | rows | Extra |
+----+--------------------+------------+-------+---------------+----------+-----
----+------+-------+--------------------------+
| 1 | PRIMARY | file | index | NULL | filesize | 8
| NULL | 21168 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | <derived3> | ALL | NULL | NULL | NULL
| NULL | 2813 | Using where |
| 3 | DERIVED | file | index | NULL | filesize | 8
| NULL | 21168 | Using index |
+----+--------------------+------------+-------+---------------+----------+-----
----+------+-------+--------------------------+
3 rows in set (0.01 sec)