mysql查询慢如何解决,如何优化此MySQL慢（非常慢）查询？

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.

解决方案

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.