Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Optimization Help 3sec+ query


mrcold213

Recommended Posts

MySQL 4.1.20

560000 Products 556 Categories

 

 

 

This query takes me 3.2 seconds on average, any ideas on how to optimize it?

 

====================================================================

SELECT p.products_image, pd.products_name, p.products_id, p.products_price, p.products_tax_class_id,
IF (
s.status, s.specials_new_products_price, NULL
) AS specials_new_products_price,
IF (
s.status, s.specials_new_products_price, p.products_price
) AS final_price
FROM products_description pd, products p, products_to_categories p2c
LEFT JOIN specials_retail_prices s ON p.products_id = s.products_id
LEFT JOIN categories c ON c.categories_id = p2c.categories_id
WHERE p.products_status = '1'
AND c.categories_status = '1'
AND p.products_id = p2c.products_id
AND pd.products_id = p2c.products_id
AND pd.language_id = '1'
AND p2c.categories_id = '421'
ORDER BY final_price
LIMIT 0 , 20

====================================================================

Link to comment
Share on other sites

MySQL 4.1.20

560000 Products 556 Categories

This query takes me 3.2 seconds on average, any ideas on how to optimize it?

 

====================================================================

SELECT p.products_image, pd.products_name, p.products_id, p.products_price, p.products_tax_class_id,
IF (
s.status, s.specials_new_products_price, NULL
) AS specials_new_products_price,
IF (
s.status, s.specials_new_products_price, p.products_price
) AS final_price
FROM products_description pd, products p, products_to_categories p2c
LEFT JOIN specials_retail_prices s ON p.products_id = s.products_id
LEFT JOIN categories c ON c.categories_id = p2c.categories_id
WHERE p.products_status = '1'
AND c.categories_status = '1'
AND p.products_id = p2c.products_id
AND pd.products_id = p2c.products_id
AND pd.language_id = '1'
AND p2c.categories_id = '421'
ORDER BY final_price
LIMIT 0 , 20

====================================================================

 

Use couple the cache contributions

http://www.oscommerce.com/community/contributions,5029

http://www.oscommerce.com/community/contributions,2873

 

then the number of products/categories becomes irrelevant.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...