mysql自连接查询,优化MySQL自连接查询

I need to select pairs of rows from a table that meet certain criteria.

Here's an SQL fiddle that demonstrates what I want:

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

解决方案

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

The solution is simple, here it is:

http://sqlfiddle.com/#!2/56deb/1/0

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 )

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

Regards,