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
