SQL Query taking too long

Posted by user345426 on Stack Overflow See other posts from Stack Overflow or by user345426
Published on 2010-05-19T19:35:59Z Indexed on 2010/05/19 19:40 UTC
Read the original article Hit count: 178

Filed under:
|

I am trying to optimize the SQL query listed below.
It is basically a search engine code that retrieves products based on the products name. It also checks products model number and whether or not it is enabled.
This executes in about 1.6 seconds when I run it directly through the phpMyAdmin tool but takes about 3 seconds in total to load in conjunction with the PHP file it is placed in.

I need to add a category search functionality and now that is crashing the MySQL server, HELP!

SELECT DISTINCT p.products_id                ,
                p.products_image             ,
                p.products_price             ,
                s.specials_new_products_price,
                p.products_weight            ,
                p.products_unit_quantity     ,
                pd.products_name             ,
                pd.products_img_alt          ,
                pd.products_affiliate_url
FROM            products AS p
                LEFT JOIN vendors v
                ON              v.vendors_id = p.vendors_id
                LEFT JOIN specials AS s
                ON              s.products_id = p.products_id
                AND             s.status      = 1,
                                categories             AS c  ,
                                products_description   AS pd ,
                                products_to_categories AS p2c
WHERE           (
                                (
                                                pd.products_name LIKE '%cleaning%'
                                AND             pd.products_name LIKE '%supplies%'
                                )
                OR
                                (
                                                p.products_model LIKE '%cleaning%'
                                AND             p.products_model LIKE '%supplies%'
                                )
                OR              p.products_id                                                              = 'cleaning supplies'
                OR              v.vendors_prefix                                                           = 'cleaning supplies'
                OR              CONCAT( CAST(v.vendors_prefix AS CHAR), '-', CAST(p.products_id AS CHAR) ) = 'cleaning supplies'
                )
AND             p.products_status   = '1'
AND             c.categories_status = '1'
AND             p.products_id       = pd.products_id
AND             p2c.products_id     = pd.products_id
AND             p2c.categories_id   = c.categories_id
ORDER BY        pd.products_name

© Stack Overflow or respective owner

Related posts about mysql

Related posts about php