GROUP BY with ORDER BY without using filesort/tmp table

MySQL & MariaDB Other MySQL® Questions
1

Hi,

No matter what I do, I cannot get this query NOT to use filesort/tmp table.

SELECT … FROM messages WHERE author=1 AND flag=0 GROUP BY checksum, recipient ORDER BY timestamp DESC LIMIT 10;

Is it possible to get this query to use INDEX/WHERE only?

The index is on:
INDEX(author, flag, checksum, recipient, timestamp)

2

Not really, because you do group by one set of columns and sort groups by unrelated column.

Think how would you execute such query without using temporary table and file sort ? If you do not see obvious way MySQL quite likely can’t do anything ether.

Note your query is also possibly wrong as you sort by timestamp but not all iterms in the group may have same timestamp.

3

Thanks Peter, I thought that was the case!