Reputation: 4225
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
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
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