优化MySQL自连接查询 [英] Optimize MySQL self join query

问题描述

我需要从表中选择满足特定条件的成对行.
这是一个SQL小提琴，演示了我想要的东西:

I need to select pairs of rows from a table that meet certain criteria.
Here's an SQL fiddle that demonstrates what I want:

http://sqlfiddle.com/#!2/4fc2f/16/0

这在表很小的时候很好用-但问题是我的生产表在特定日期可能有数百万行和数千行.查询现在需要14到15秒才能在特定日期运行.我该如何改善?

This works well when the table is small--but the problem is my production table may have millions of rows and thousands of rows for a particular date. Query takes 14-15 seconds to run for a particular date now. How can I improve it?

MySQL 5.5

MySQL 5.5

推荐答案

您缺少索引.尝试使用EXPLAIN分析您的查询，这将对您有很大帮助.

you are missing an index. Try using EXPLAIN to analyze your query, it will help you a lot.

解决方案很简单，这里是: http://sqlfiddle.com/#!2/56deb/1/0

The solution is simple, here it is: http://sqlfiddle.com/#!2/56deb/1/0

您需要添加一个索引，其中包括where语句中使用的列，以便使用它们:

You need to add an index that includes the columns used in the where statement in order they are used:

KEY `night_of_2` (`night_of`,`student_id`,`check_class`)

另外，由于要将表自身联接，因此还需要在联接上强制使用索引:

Also you need to force the use of the index on the join, since you are joining the table to itself:

JOIN checks checks2

FORCE INDEX(night_of_2)ON(checks1.night_of = checks2.night_of)

FORCE INDEX ( night_of_2 ) ON ( checks1.night_of = checks2.night_of )

(如果我想知道一种更好的方法):)

(if there is a better way i would like to know about it) :)

此致

这篇关于优化MySQL自连接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！