Geek asks and answers

Problem with mysql query - nested and / or statements

I have this query that works 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 look for those WHERE clauses, or, given the numerical value of the keyword, as in the example, look for the product identifier by simply adding this to the previous query:

 OR (p.products_id=3220)

But for some reason, this add-on freezes the mysql server. It just continues to fulfill the request, and it never ends. I have to manually restart the server. What am I doing wrong?

+3
source share
3 answers

OR ( , ), , JOIN, WHERE (p.products_id = pd.products_id, p.products_id = p2c.products_id p2c.categories_id = c.categories_id). , (, , , , , products_to_categories)? - :

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)

, p.products_status = 1 AND pd.language_id = 1 , , JOIN.

+4

OR , AND. WHERE, .

: bu , LIKE, - products_id. UNION . , OR. UNION ALL , , .

+1

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

0
source

More articles:


All Articles