I agree with Brent on what MySQL is doing ... are you seeing poor
performance with this query? If so, you might evaluate whether adding
an index on your 'post_date' column improves things, as MySQL may be
able to sort and thus LIMIT more quickly (using index in RAM rather than
reading off disk). It may not make a difference unless your result set
from the "WHERE ID IN ()" portion is reasonably large, though.
Dan
Brent Baisley wrote:
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]
--
Dan Buettner
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
