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.

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:
>
>

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.

On Oct 4, 3:58 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> 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.
Wouldn't this prevent me from using the index on sort_field, though?

On 4 Oct, 15:27, nk <nkoell...@gmail.com> wrote:
> On Oct 4, 3:58 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:> 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.
>
> Wouldn't this prevent me from using the index on sort_field, though?

Yes, but I don't know what the data looks like. It may be quicker to
do a filesort than to have to read all the records in main_table.

How many records in second_table have l.field2 = "abcde"?

On Oct 4, 4:44 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 4 Oct, 15:27, nk <nkoell...@gmail.com> wrote:
>
> > On Oct 4, 3:58 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:> 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.
>
> > Wouldn't this prevent me from using the index on sort_field, though?
>
> Yes, but I don't know what the data looks like. It may be quicker to
> do a filesort than to have to read all the records in main_table.
>
> How many records in second_table have l.field2 = "abcde"?
Around 1 million.