How to avoid multiple joints for better performance?

advertisements

I have 5 tables and all have dependencies so i have written this query for fetching the data and its working fine but the performance is bad so can you suggest some concepts?

SELECT  a.*,cg.*,c.*,qt.*,ct.*
FROM MYB.choice_table ct
RIGHT OUTER JOIN MYB.question_table qt ON ct.questionID = qt.questionID
RIGHT OUTER JOIN MYB.category_table c ON  c.categoryID = qt.categoryID
RIGHT OUTER JOIN MYB.categoryGroup_table cg ON cg.categoryGroupID=c.categoryGroupID
RIGHT OUTER JOIN MYB.audit_table a ON a.auditID=cg.auditID
WHERE a.auditID=123


I would suggest you to have a look at the execution plan using EXPLAIN. See http://dev.mysql.com/doc/refman/5.7/en/explain.html.

Such execution plan will help you understand what is going on under the hood and identify potential issues. The join count itself isn't slowing things down.