如何优化此MySQL慢(非常慢)查询? [英] How to optimize this MySQL slow (very slow) query?

问题描述

我有一个具有500k行的2 gb 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

文件排序需要50到70秒才能完成.

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

在按工资单删除订单并在应用程序中进行排序时，总运行时间(包括排序)减少到大约25-30秒.但这还太多了.

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?

谢谢.

推荐答案

将名称列表拖放到临时表中，然后在两个表上进行内部联接.这种方式比为每一行合并整个列表要快得多.这是伪代码:

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

还要注意，name应该有一个索引.这样可以使事情快得多.感谢托马斯做笔记.

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

这篇关于如何优化此MySQL慢(非常慢)查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！