Thanks for the replies. The database is basically read-only at the moment, so OPTIMIZE TABLE didn't do anything.
When I force the key to be fullname for the second problem, it runs even worse. It's not practical to create an additional email,fullname index because in my app I actually have 3! combinations of filters and sort orders. Adam On Apr 20, Alexey Polyakov wrote: > On 4/20/06, Adam Wolff <[EMAIL PROTECTED]> wrote: > > > How can I optimize the case where I filter on one key but sort on another? > > This is fast: > > SELECT * FROM contacts WHERE fullname LIKE "j%" ORDER BY fullname LIMIT > > 10; > > > > But this is slow: > > SELECT * FROM contacts WHERE fullname LIKE "j%" ORDER BY email LIMIT 10; > > > > EXPLAIN tells me that the optimizer is using filesort for the second > > but not the first (which makes sense.) > > Such things are pretty hard to optimize. If you have large number of > rows, forcing usage of (email) index for this query might help, as > engine will scan as many rows as required for satisfying limit. Also > having (email, fullname(1)) index might save a few cycles. > > > * Question 2: > > Why does introducing an extra WHERE clause make things slower? > > If I do this: > > SELECT * FROM contacts WHERE fullname LIKE "j%" AND user_id=1 > > ORDER BY fullname LIMIT 10; > > Probably because the engine is not using (fullname) index for this > query - optimizer sees 'const' ref for user_id, retrieves all rows > that have user_id=1 and then filters/sorts them all. > > -- > Alexey Polyakov > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
