Joining couples, red candidates, and blue candidates

I have 3 tables:

Table couples         consist of save_id, id_candidate_red, id_candidate_blue
    Table candidates_red  consist of save_id, id_candidate, enter_match
    Table candidates_blue consist of save_id, id_candidate, enter_match

And I am running the query

SELECT  *
    FROM  couples as c

    LEFT JOIN candidates_blue as cb 
        ON cb.save_id = 3
            AND cb.user_id = c.id_candidate_blue

    LEFT JOIN candidates_red as cr 
        ON cr.save_id = 3
            AND cr.user_id = c.id_candidate_red

    WHERE c.save_id = 3
        AND cb.enter_match = 1
        AND cr.enter_match = 1

…which seems pretty simple to me, but as I have a big dataset behind my tables, it takes quite a while to execute.

I'm afraid there's not much we can do to help with this kind of question. There's not much obviously wrong. You likely need to run the query analyzer and add some missing indices and keys. Regardless, there's still an opportunity for improvement, albeit not much performance wise...

SELECT  *

Do you really need every column? Even with proper keys/indices in place, this will typically force a table scan. Explicitly state only the fields you need to return. It will result in less I/O and could possibly turn a scan operation into a seek. If it doesn't, you'll need to find the missing index to be added.

The next thing you can do is remove the duplication.

LEFT JOIN candidates_blue as cb 
    ON cb.save_id = 3
        AND cb.user_id = c.id_candidate_blue

LEFT JOIN candidates_red as cr 
    ON cr.save_id = 3
        AND cr.user_id = c.id_candidate_red

WHERE c.save_id = 3
    AND cb.enter_match = 1
    AND cr.enter_match = 1

I suppose you added all the save_id = 3s in an attempt to speed up the query? Don't bother. It won't help. Specifying it once for the couples table is sufficient. Which actually brings me to an optimization. Your query is equivalent to using INNER JOIN, but without any of the benefits. You're not currently getting the unmatched records anyway, so you might as well switch. You might see a significant performance increase.

SELECT  * /* don't forget to specify the fields you actually want */
FROM  couples as c

INNER JOIN candidates_blue as cb 
    ON cb.user_id = c.id_candidate_blue

INNER JOIN candidates_red as cr 
    ON cr.user_id = c.id_candidate_red

WHERE c.save_id = 3
    AND cb.enter_match = 1
    AND cr.enter_match = 1

One last thing: Very nice formatting! It's extremely easy on the eyes.

StackExchange Code Review Q#102719, answer score: 2