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

Question

I have a mysql table with 2 gb 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

A file port is required and between 50 and 70 seconds.

When you delete the salary order and perform sorting in the application, the total execution time (including sorting) is reduced to approximately 25-30 seconds. But that is still too much.

Any idea how I can speed this up?

Thanks.

Answer 1

Drop the list of names into the temporary table, and then internally join the two tables. This method is much faster than combing the entire list for each line. Here's the pseudo code:

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

, name . . .

Answer 2

:

• *, ?
• , ,
• , LIKE ('n%')

Answer 3

, . . .

name mytable, ?

Answer 4

, WHERE, (, ) (, ), , , .

sort_buffer_size. EXPLAIN.

Answer 5

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

"IN" , :

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

, .