using OR in join condition

[hessodreamy]

I Have this query which just takes too long and kills the server.
I have 2 left joins and want to do an inner join matching values in either of the left joins. It seems to be the use of an OR in this inner join which is the bottleneck.

select * from 
categories c
    INNER JOIN cat_subCat csc ON c.catId = csc.relCatId
    INNER JOIN subCategories s ON csc.relSubCatId = s.subCatId

    LEFT JOIN
    (
    SELECT sb_th.relSubCatId, thunderCatName, keywords AS th_keywords, relProdId as th_prod
    FROM subcat_thunderCat sb_th
    INNER JOIN thunderCategories th ON sb_th.relThunderId = th.thunderCatId
    INNER JOIN prod_thunderCat p_th ON th.thunderCatId = p_th.relThunderCatId
    ) as th_join
    ON s.subCatId = th_join.relSubCatId

    LEFT JOIN 
    prod_subCats psc ON s.subCatId = psc.relSubCatId

    INNER JOIN tProducts p ON th_prod= p.productID OR psc.relProdId = p.productID

Am I doing something wrong? Is there an alternative way of making this join?

I've also posted the explain data. I think the 'Range checked for each record' refers to the OR join.

id select_type table type possible_keys key key_len ref rows Extra 
1 PRIMARY c ALL PRIMARY    65  
1 PRIMARY csc ref subid,catid catid 4 hsdmain.c.catId 6  
1 PRIMARY s eq_ref PRIMARY PRIMARY 4 hsdmain.csc.relSubCatId 1  
1 PRIMARY  ALL     1511  
1 PRIMARY psc ref subid subid 4 hsdmain.s.subCatId 7  
1 PRIMARY p ALL PRIMARY    27439 Range checked for each record (index map: 0x1) 
2 DERIVED sb_th ALL     115  
2 DERIVED th eq_ref PRIMARY PRIMARY 4 hsdmain.sb_th.relThunderId 1  
2 DERIVED p_th ALL     1464 Using where

 

[r937]

you are getting large volumes due to cross join effects

as far as i can tell (pretty tricky without actually knowing all of your tables intimately), your OR condition will join every product that is returned by the relProdId column of the thunder subquery, as many times as there are prod_subCats rows related to the subCategories table, regardlress of whether it's the same product related in the prod_subCats table

so yeah, i think you are doing something wrong



r937.com | rudy.ca