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]
