CODEWITHSUNDEEP
mysqlsqljoinmariadb
Akash Sourav Nayak
Akash Sourav Nayak

Reputation: 203

How to avoid Multiple joins for better performance?

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

Upvotes: 0

Views: 3275

Answers (2)

Dheebs
Dheebs

Reputation: 408

The only thing I can immediately think of is to index all the data you use for your joins.

You should also not do 

Select *

Minimize the returns to what you need

Sub Note:

Looking at your query it is going to join the whole data set and THEN search through it for your where clause.

So let's say a.123 has only 50 values but all your tables have 10000 rows of data. You are joining n amount if tables of 10000 rows each x how ever many columns to return the search through and get 50 values

Upvotes: 1

Kousalik
Kousalik

Reputation: 3137

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.

Upvotes: 1

Related Questions