On 10/10/06,
tv@fuzzy.cz <
tv@fuzzy.cz> wrote:
SELECT id, (
SELECT allowed FROM rights WHERE user_id = 1 AND (
(firm_id = projects.firm_id AND project_id = projects.id AND
subproject_id IS NULL)
OR (firm_id = projects.firm_id AND project_id IS NULL)
)
ORDER BY firm_id, project_id, subproject_id, module_id, allowed DESC LIMIT 1
) as allowed
FROM projects;
The problem is in the 'LIMIT 1' clause - that's the reason I can't write that as
a join.
Does someone else has an idea how to solve this? If needed I can send more
complex examples and some testing data, explain plans, etc.
I've been thinking about some 'intermediate table' with results of the
subselect, updated by a set of triggers, but maybe there's some better
solution.
I think your problem is NOT the LIMIT, it is the fact that you are putting a select inside the select block (your correlated subquery). You should try left outer joins instead:
SELECT
projects.id,
rights.allowed
FROM projects
LEFT OUTER JOIN rights ON (
(
rights.firm_id = projects.firm_id
AND rights.project_id =
projects.id AND rights.subproject_id IS NULL
) OR (
rights.firm_id = projects.firm_id
AND rights.project_id IS NULL
)
)
GROUP BY
projects.id,
rights.allowed
Using correlated subqueries is really bad (IMHO) because it causes your query to perform a select for each row returned. I have never seen a correlated subquery that cannot be refactored into a join (this is a challenge for any of you who disagree - I would love to see a reason to use a correlated subquery).
You can get more information about correlated subqueries and performance at
http://www.bcarter.com/sap29.htm .
Also, I would consider putting a conditional unique constraint to enforce your rule on which fields must be null/not null to help preserve your data integrity.
Hope this helps!
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com http://codeelixir.com==================================================================