MySQL is doing a file sort on the query result. It's not sorting the entire table and it's not sorting the 40 record limit you
specified. It's sorting the WHERE id IN... result. After the sort, then it will return just the first 40 records.
You can throw and EXPLAIN in front of the query to see what mysql is going to
do.
----- Original Message -----
From: "Peter Van Dijck" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Tuesday, June 20, 2006 3:08 AM
Subject: Query question: select * from table where id in (1,2,3) order by date
uses FILESORT
Hi all,
this is a problem I'm running into:
A table has like 400,000 rows, with a primary key index id. I use this query:
SELECT * FROM table WHERE id IN (58, 25, 75, ...) ORDER BY post_date
DESC LIMIT 0, 40
The problem is, it uses the index id and then a FILESORT.
Questions:
1) Am I correct in thinking that a filesort on this large table is
bad? Or is mysql filesorting only the 40-row result set, and itt's not
so bad?
2) Any solutions? Is this a common problem?
Thanks,
Peter
--
find videoblogs: http://mefeedia.com
my blog: http://poorbuthappy.com/ease/
my job: http://petervandijck.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
