MySQL - Speeding up large query

[MrManager]

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

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.

[phranque]

i would try the following and see if it fixes the problem:

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.

[eelixduppy]

You might also want to take a look at the optimization documentation [dev.mysql.com] at mysql.com. There are some very nice ideas in there that make major improvements.

[SeanW]

How many rows will be coming into the sort, compared with the number of rows not having field2="abcde", or the number of rows having to be joined?

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)

Sean

(edit: clarity in the first para)

[edited by: SeanW at 2:44 pm (utc) on Oct. 6, 2007]

[MrManager]

Thanks for the replies!

I ended up removing the join completely and adding the field from the second table to the main table as well. It will probably cost me some disk space, but the query only takes a few seconds now.