Only activate LEFT JOIN lookup if another LEFT JOIN variable is TRUE?

[tosta]

I want to do a PHP MySQL query performance efficient. So If I have a lot of LEFT JOIN’s, I do not need them all the time, only if other conditions are met by another LEFT JOIN.

So is it even possible to run LEFT join in a query only if another left join variable meets the need.

Example:

LEFT JOIN page ON main.id=page.id and main.type=0
LEFT JOIN permission ON permission.pageid=page.id and permission.userid=? and page.state='MembersOnly' 

So I would like to run The Second LEFT JOIN db check ONLY if the first JOIN PageState equals to “MembersOnly”.

This code checks both JOINS always/all the time, and maybe more efficient is to add separate queries inside a loop if these rare conditions are met, or else, in the end there would be like 10 of extra JOINS.

I don’t think it is possible, but I am not an expert so I decided to ask.

If anyone knows more please let me know,

Thank you!

[r937]

one way is to use a UNION ALL of two queries…

the first query selects rows based on page.state='MembersOnly' and the second query selects rows without that join

i’d mock it up for you but you didn’t post the entire query

[tosta]

if using UNION ALL all the JOINS are actively looking for matches, it does not disable the LEFT JOIN and runs even if the page.state!='MembersOnly', it does not solve it the best way. However, only thing that UNION can do is to limit the number of requests to db, so that the LEFT JOIN will be ran less frequently, because in the union query I can add more individual conditions to the main query… I need to test that theory.

to more clarify I wanted this:

LEFT JOIN page ON main.id=page.id and main.type=0
if (page.state='MembersOnly') 
LEFT JOIN permission ON permission.pageid=page.id and permission.userid=?
else 
///do nothing

Every extra added join adds around 0.0030 seconds execution time, so that is my problem, i have like 20+ joins and about 10 are permission joins, Union may limit requests I hope.

Thank you for the idea!

[r937]

@tosta

if using UNION ALL all the JOINS are actively looking for matches, it does not disable the LEFT JOIN

perhaps you did not understand what i meant

SELECT ...
  FROM main
INNER
  JOIN page 
    ON page.id = main.id
 WHERE main.type=0
   AND page.state <> 'MembersOnly' 

UNION ALL

SELECT ...
  FROM main
INNER
  JOIN page 
    ON page.id = main.id
LEFT
  JOIN permission 
    ON permission.pageid = page.id 
   AND permission.userid = ?    
 WHERE main.type=0
   AND page.state = 'MembersOnly' 

see? the second SELECT runs the LEFT JOIN, the first one doesn’t

and the two subquery results are of course mutually exclusive because one uses page.state <> 'MembersOnly' while the other uses page.state = 'MembersOnly'

[tosta]

I finally understood, after getting more into it.

Thank you again