SQL Query taking too long

Question

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!

Justin

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

Answer 1

If you have many rows, then you should use Mysql Indexes. Google about them, it can make query much faster.

Answer 2

left join specials as s on s.products_id = p.products_id and s.status = 1,

there you've a full table scan. That kind of data (tinyint, true/false) are not good handled by InnoDB. In Oracle you have something like a bitmap index, that is good for low selectivity indexes. You could use a subquery, in your where, something like this:

...
where 
   p.product_id IN (SELECT S.status FROM specials s WHERE s.status = 1)
AND
   (
          ( pd.products_name like '%cleaning%' and pd.products_name like '%supplies%' ) or 
...

Then, you pray for the query cache to cache the results from that subquery.

POST YOUR EXPLAIN!