On 20/12/2005, Marcus Bointon wrote: > 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?
To avoid the filesort, you need a composite index on (account, lastname, firstname) instead of separate indexes on account, lastname and fistname. -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
