Hi Marcus, Do you have the output of the EXPLAIN? I've found the number of rows processed to be very instructional. If you could get this number down, it may be well worth putting an index on the account column and re-running the EXPLAIN to see how many rows are being processed.
I recently put an index on a table that was doing a filesort around 50 times for each web page. It was processing 58800 records each sort x 50 made for a very slow web page. I put an index in place, it still does the filesort but is only processing 154 rows each time and this is probably being done in memory as it is so tiny. Would be worth a punt. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -----Original Message----- From: Marcus Bointon [mailto:[EMAIL PROTECTED] Sent: Tuesday, 20 December 2005 9:55 PM To: mysql@lists.mysql.com Subject: Slow sorting I have a table that contains around 400,000 simple names. It's displaying a subset of them (perhaps 5,000) them a page at a time in a web interface, sorted by name, so I have a query like this: SELECT * FROM names WHERE account=123 ORDER BY lastname, firstname LIMIT 0,30 That takes 11 seconds to run. Without the order by it takes 0.13 sec. I have simple indexes on both first name and last name (they are sometimes searched separately). It strikes me that this is really very slow - it really doesn't have much to sort. I tied doing an explain, and though I could see that it was using the indexes, it was also saying use where, use temporary, use filesort. Why is it falling back to these methods? How can I make this faster? A DBA friend recommended using clustered indexes - does MySQL have such things? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- 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]
