| GROUP BY with ORDER BY without using filesort/tmp table [message #682] |
Wed, 07 February 2007 14:10  |
 Speeple
Messages: 91
Registered: August 2006 |
Member |
|
|
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)
Martin Gallagher | Speeple: The latest news
|
|
|
| Re: GROUP BY with ORDER BY without using filesort/tmp table [message #688 is a reply to message #682 ] |
Thu, 08 February 2007 05:57  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
|
Members
Search
Help
Register
Login
Home
