mysql query problem - nested and/or statements

Question

I have this query which runs fine:

SELECT count(distinct p.products_id) as total 
FROM (products p 
        LEFT JOIN manufacturers m 
        USING(manufacturers_id), products_description pd, 
              categories c, products_to_categories p2c ) 
LEFT JOIN meta_tags_products_description mtpd 
          ON mtpd.products_id= p2c.products_id 
          AND mtpd.language_id = 1 
WHERE (p.products_status = 1 AND p.products_id = pd.products_id 
       AND pd.language_id = 1 AND p.products_id = p2c.products_id 
       AND p2c.categories_id = c.categories_id 
       AND (
            (pd.products_name LIKE '%3220%' 
             OR p.products_model LIKE '%3220%' 
             OR m.manufacturers_name LIKE '%3220%' 
             OR (mtpd.metatags_keywords LIKE '%3220%' 
                 AND  mtpd.metatags_keywords !='') 
             OR 
             (mtpd.metatags_description LIKE '%3220%' 
              AND   mtpd.metatags_description !='') 
             OR 
              pd.products_description LIKE '%3220%'
            ) 
           ) 
      )

But I want to either search for those WHERE clauses or, given a numeric keyword value as in the example, search for the products id by just adding this to the previous query:

 OR (p.products_id=3220)

But for some reason that addition freezes the mysql server. It just keeps executing the query and it never ends. I have to manually restart the server. What is it that I am doing wrong?

Wow. You know you are allowed to align and indent SQL too, right? — GolezTrol
– GolezTrol, Commented May 21, 2011 at 16:39
Where, exactly, are you adding the OR? After the last parenthesis? — squawknull
– squawknull, Commented May 21, 2011 at 16:46
Your from/joins are really curious... I have to run but plan on checking this out later. — Fosco
– Fosco, Commented May 21, 2011 at 16:47
Yes, sorry, I forgot to format the SQL a bit. Thanks for the job Fosco. It is not really my query, not all of it at least, it is a query composed by ZenCart, I am just modifying it. — AJJ
– AJJ, Commented May 21, 2011 at 18:28
Yes, I am currently adding the OR statement after the last parenthesis of the query. — AJJ
– AJJ, Commented May 21, 2011 at 18:31

squawknull · Accepted Answer · 2011-05-21 16:52:22Z

5

When you add the OR to the end (assuming you're adding it after the last paren), you are creating a cartesian join as you're invalidating your JOIN conditions that you placed in your WHERE clause (p.products_id = pd.products_id, p.products_id = p2c.products_id and p2c.categories_id = c.categories_id). Also, why are you putting the initial tables (products, manufacturers, products_description, categories, products_to_categories) inside of parenthesis? Instead, try something more like this:

SELECT count(distinct p.products_id) as total 
FROM products p 
LEFT JOIN manufacturers m USING(manufacturers_id)
LEFT JOIN products_description pd using products_id
LEFT JOIN categories c USING (categories_id)
LEFT JOIN products_to_categories p2c USING (products_id)
LEFT JOIN meta_tags_products_description mtpd 
          ON mtpd.products_id= p2c.products_id 
          AND mtpd.language_id = 1 
WHERE (p.products_status = 1  
       AND pd.language_id = 1  
       AND (
            (pd.products_name LIKE '%3220%' 
             OR p.products_model LIKE '%3220%' 
             OR m.manufacturers_name LIKE '%3220%' 
             OR (mtpd.metatags_keywords LIKE '%3220%' 
                 AND  mtpd.metatags_keywords !='') 
             OR 
             (mtpd.metatags_description LIKE '%3220%' 
              AND   mtpd.metatags_description !='') 
             OR 
              pd.products_description LIKE '%3220%'
            ) 
           ) 
      )
      OR (p.products_id=3220)

I do not understand what p.products_status = 1 AND pd.language_id = 1 mean in your case, so you may need to adjust how those are included in the JOIN.

answered May 21, 2011 at 16:52

squawknull

5,2122 gold badges19 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AJJ Over a year ago

I can't really modify the query that much. Well, I guess I could, but it forces me to modify a lot more php code. Can you please suggest a way to add that OR statement without modifying the rest of the query (or as less as possible)?. Anyway, I tried your solution and seems to work fine. I can't use the LEFT JOIN categories c USING categories_id because that field is only present in categories, not in the other tables.

squawknull Over a year ago

According to your original query, the categories_id field exists in both the categories table and the products_to_categories table. I would strongly suggest modifying the code to clean up the query, but if you really cannot do that, just add the join conditions to the OR clause as well, such as

OR (p.products_id = pd.products_id AND p.products_id = p2c.products_id AND p2c.categories_id = c.categories_id AND p.products_id=3220)

. That should at least solve your cartesian join problem, but that makes for one woefully ugly query.

GolezTrol · Accepted Answer · 2011-05-21 16:45:31Z

1

Using OR is slower than using AND. You could try in this case, to put the new condition at the beginning of the WHERE instead of at the end.

Another solution would bu two write two separate queries, one with all the LIKEs and another using the products_id. Then, use UNION to combine the result of those queries. This solution is often faster than using complex expressions with OR. UNION ALL is even faster theoretically, but it may result in double records if they match the conditions in both queries.

answered May 21, 2011 at 16:45

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

3 Comments

AJJ Over a year ago

I can't really modify the query in that way. I should probably have explained that this query is (most of it) composed via PHP by ZenCart. I can modify bits of it, but can't run two separate queries as far as I can see.

AJJ Over a year ago

What exactly is the benefit of putting the OR at the beginning? Speed only?

AJJ Over a year ago

I gave this a try, but I get the same result: mysqld stops responding and I have to restart it by hand. Any idea why this happens? If the query returned no rows or an error, I could do something, this way I can't do much. Is there a way to debug this? Is there a way perhaps to know what the last query run by the server did?

Orin Moyer · Accepted Answer · 2017-03-08 18:35:52Z

0

I know it is not always possible, but if you can change your LEFT JOIN to INNER JOIN the nested AND / OR clauses will work much quicker (it will not invade your join)

answered Mar 8, 2017 at 18:35

Orin Moyer

5092 gold badges7 silver badges14 bronze badges

Collectives™ on Stack Overflow

mysql query problem - nested and/or statements

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related