Optimize Mysql Self Join Query

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 This works well when

Solution 1:

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,

Solution 2:

Your indexes aren't particularly effective - you can have more than one column in an index. But in order to get the right indexes you need to look at every query you run on the database, the frequency of each one, and the distribution of the data. Or are you just asking us to do your homework? Based on the information provided, the query would be more efficient with an index on (night_of, check_class, student_id) and lose the existing indexes apart from the PK.