On 4 Oct, 14:11, nk <nkoell...@gmail.com> wrote:
> 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.

Well,
since your where clause operates on field2 of second_table, could you
swap the tables round and use an index on field2 to limit the rows to
be operated on?

As it is, all the rows in main_table have to be read, a JOIN attempted
into second_table and, if the JOIN is successful, field2 must be
examined.