How to optimize this MySQL slow (very slow) query?

Question

I have a 2 gb mysql table with 500k rows and I run the following query on a system with no load.

select * from mytable 
where name in ('n1', 'n2', 'n3', 'n4', ... bunch more... ) 
order by salary

It takes a filesort and between 50 and 70 seconds to complete.

When removing the order by salary and doing the sorting in the application, the total runtime (including the sorting) cuts to about 25-30 seconds. But that's still far too much.

Any idea how I can speed this up?

Thank you.

Answer 1

Drop the list of names into a temporary table and then do an inner join on the two tables. This way is much faster than combing that entire list for each row. Here's the pseudocode:

create temporary table names
    (name varchar(255));

insert into names values ('n1'),('n2'),...,('nn');

select
    a.*
from
    mytable a
    inner join names b on
        a.name = b.name

Also note that name should have an index on it. That makes things go much faster. Thanks to Thomas for making this note.

Answer 2

create index xyz on mytable(name(6));

"IN" queries are almost alway inefficient, as they are conceptually processed like this:

select * from mytable where name = n1  
or name = n2
or name = n3
...

The index I've given above may mean the query optimizer accesses the rows by index instead of table scan.

Answer 3

Some ideas:

• Do you need to be selecting *, can you get away with only selecting a subset?
• If you can get away with selecting a subset, you could add a covering index, that is already sorted by salary
• If everything has the same pattern you could do LIKE('n%')

Answer 4

Try selecting the rows you want using a subquery, and then order the results of that subquery. See this question.

And you do have an index on name in mytable, right?

Answer 5

Depending on the data distribution and the amount of rows your WHERE clause matches, you may want to try an index on (salary, name) or even (name, salary) although the latter will most probably not be very useful for that kind of queries.

You may also want to increase your sort_buffer_size setting. Test everything seperately and compare the output of EXPLAIN.