PHWinfo - Afficher un message

04/10/2007, 14h11

I have two tables with approximately 5 mio rows each, on which I'm
trying to execute the following query:

Code:

SELECT field1
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.

04/10/2007, 14h11	#1
nk Messages: n/a Hébergeur:	Speeding up large query I have two tables with approximately 5 mio rows each, on which I'm trying to execute the following query: Code: SELECT field1 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.