CODEWITHSUNDEEP
mysqlsql
Vijay Sebastian
Vijay Sebastian

Reputation: 1204

Sql Join taking a lot of time

I am tying to execute this query but it is taking more than 5 hours, but the data base size is just 20mb. this is my code. Here I am joining 11 tables with reg_id. I need all columns with distinct values. Please guide me how to rearrange the query. 

SELECT * 

FROM degree

JOIN diploma 
 ON degree.reg_id = diploma.reg_id
JOIN further_studies 
 ON diploma.reg_id = further_studies.reg_id
JOIN iti 
 ON further_studies.reg_id = iti.reg_id
JOIN personal_info 
 ON iti.reg_id = personal_info.reg_id
JOIN postgraduation 
 ON personal_info.reg_id = postgraduation.reg_id
JOIN puc 
 ON postgraduation.reg_id = puc.reg_id
JOIN skills 
 ON puc.reg_id = skills.reg_id
JOIN sslc 
 ON skills.reg_id = sslc.reg_id
JOIN license 
 ON sslc.reg_id = license.reg_id
JOIN passport 
 ON license.reg_id = passport.reg_id
GROUP BY fullname

Please help me if I did any mistake

Upvotes: 0

Views: 113

Answers (2)

Suresh Alathur
Suresh Alathur

Reputation: 93

I thought the problem is in the query.First make sure group by fullname and try to give some column names instead of *.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

This is a bit long for a comment.

The first problem with your query is that you are using select * with group by fullname. You have zillions of columns in the select that are not in the group by. Unless you really, really, really know what you are doing (which I doubt), this is the wrong way to write a query.

Your performance problem is undoubtedly due to cartesian products and lack of indexes. You are joining across different dimensions -- such as skills and degrees. The result is a product of all the possibilities. For some people, the data size can grow and grow and grow.

And then, the question is: do you have indexes on the keys used in the joins? For performance, you generally want such indexes.

Upvotes: 6

Related Questions