Welcome to WebmasterWorld Guest from 220.127.116.11
Forum Moderators: open
FROM main_table USE INDEX (sort_field)
LEFT JOIN second_table l USING (primary_key_field)
WHERE l.field2 = "abcde"
ORDER BY sort_field ASC LIMIT 5000, 3000
I already optimized the query by adding "USE INDEX (sort_field)" to avoid temporary tables for sorting, but when I execute it, it still locks the table for around 10 minutes. In the process list the state of the query is 'Sending data', but I doubt that the data transfer is actually what takes so long, since both servers have a pretty fast connection.
Any ideas what I could change to speed this query up? I assume the biggest problem right now is the WHERE condition, since it doesn't use an index, but I don't know how I could fix that without losing the index on sort_field.
ADD INDEX field2_index ON second_table (field2);
you might also want to limit the index length if field2 is CHAR or VARCHAR:
ADD INDEX field2_index ON second_table (field2(5));
if field2 is TEXT type, the length is required.
Add two indexes, one on primary_key_field (on each table, so 3 indexes I guess), and one on l.field2. Try getting rid of the forced index and run EXPLAIN on your query to see what MySQL is planning on doing.
If you want to avoid disk sorts you can always throw memory at it (tmp_table_size and max_heap_table_size)
(edit: clarity in the first para)
[edited by: SeanW at 2:44 pm (utc) on Oct. 6, 2007]