3
\$\begingroup\$

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.

\$\endgroup\$
4
  • 3
    \$\begingroup\$ Have you set primary keys for these tables? Do you have any indices set up? Have you analyzed your query to see which parts take the most time to execute? \$\endgroup\$ Sep 3, 2015 at 20:44
  • \$\begingroup\$ I can't see any problem with this SQL query but if you are having problem with speed you may want to have a look at a NoSQL db solution instead. \$\endgroup\$ Sep 3, 2015 at 21:33
  • \$\begingroup\$ On Code Review, we review real code, not "code like this" or hypothetical situations. This code seems realistic enough, so I've removed the hypothetical language from your question. It will be fine as long as you don't ask "but what if…" follow-up questions. \$\endgroup\$ Sep 4, 2015 at 0:27
  • \$\begingroup\$ @StefanKarlsson NoSQL databases typically suck at joins. You might be able to devise a NoSQL schema to avoid the need to join anything at all, but that sounds like a sledgehammer solution to improve performance on a very routine kind of query. \$\endgroup\$ Sep 4, 2015 at 0:32

1 Answer 1

2
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.