GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

GROUP BY with ORDER BY without using filesort/tmp table

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • GROUP BY with ORDER BY without using filesort/tmp table

    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.

    Comment


    • #3
      Thanks Peter, I thought that was the case!

      Comment

      Working...
      X