CODEWITHSUNDEEP
mysqlsqlleft-join
mr.incredible
mr.incredible

Reputation: 4225

Better way for SQL LEFT JOIN OR condition

I've done some searching without success and I want to know if there is some better way to rewrite sql query because this OR condition in the LEFT JOIN kills the performance:(

For e.g.:

SELECT DISTINCT * FROM computers
LEFT JOIN monitors ON computers.brand = monitors.brand
LEFT JOIN keyboards ON computers.type = keyboards.type
LEFT JOIN accessories ON accessories.id = keyboards.id OR accessories.id = monitors.id
GROUP BY computers.id
ORDER BY computers.id DESC

Sorry for dumb question, but is it possible to rewrite OR statement to improve performance?

Upvotes: 0

Views: 77

Answers (2)

Paul Stanley
Paul Stanley

Reputation: 4098

You are doing SELECT DISTINCT *, so its checking that your entire record is unique across all rows it gets, which is 3 tables worth. Its probably going to be already unique, if your primary keys and unique indexes are set up correctly its definitely unique, so just take it out.

If your primary keys and indexes arent setup, do that first. Primary key on fields named id.

That and SELECT * incurs a big overhead since it has to figure out what the rest of your columns are.

Guessing without knowing what the table structure actually is: Since you are grouping by GROUP BY computers.id, put that in your SELECT instead and take it out of your GROUP BY.

SELECT DISTINCT computers.id

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

I doubt it will make any difference, but you could try this:

SELECT DISTINCT * 
FROM computers
LEFT JOIN monitors ON computers.brand = monitors.brand
LEFT JOIN keyboards ON computers.type = keyboards.type
LEFT JOIN accessories ON a1.id IN (keyboards.id, monitors.id)
GROUP BY computers.id
ORDER BY computers.id DESC

You could also join to the same table twice, if you are comfortable having two sets of accessories columns (perhaps using coalesce() a bunch in the SELECT list):

SELECT DISTINCT * FROM computers
LEFT JOIN monitors ON computers.brand = monitors.brand
LEFT JOIN keyboards ON computers.type = keyboards.type
LEFT JOIN accessories a1 ON a1.id = keyboards.id 
LEFT JOIN accessories a2 ON a2.id = monitors.id
GROUP BY computers.id
ORDER BY computers.id DESC

And, fwiw, this query would not be legal in most modern database engines. If you want to GROUP BY a field, the ANSI SQL standard says you can't also just put * (even with DISTINCT) in the SELECT list, because you haven't specified which values to keep and which to discard as the database rolls up the group... the results are undefined, and that's a bad thing.

Upvotes: 1

Related Questions