How TB deal with large database usage?



  • Hi!
    For now i have PS store and deal with large database usage. Here is SO thread
    https://stackoverflow.com/questions/46359870/very-large-prestashop-mysql-query-cause-large-database-usage

    Is there a way to deal with it? Will TB deal with kind of unoptimized queries? My store have now 30000+ products and wonder when my hosting will blow up! ;)
    Any help or ideas much appreciated!!!


  • administrators

    I hope you’re using a dedicated server for that. Your hosting will definitely blow up if that’s no more than a VPS.

    I’d follow the recommendation on StackOverflow. An explain query will show which parts are slow. My guess is a missing index or a slow filesort. You can easily run an explain query by adding EXPLAIN right after the SELECT clause. Like this:

    SELECT p.*, product_shop.*, product_shop.id_category_default, pl.*, MAX(image_shop.`id_image`) id_image, il.legend, m.name manufacturer_name, MAX(pa.id_product_attribute) id_product_attribute, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity FROM `psy_category_product` cp LEFT JOIN psy_category c ON (c.id_category = cp.id_category) LEFT JOIN `psy_product` p ON p.`id_product` = cp.`id_product` INNER JOIN psy_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) LEFT JOIN `psy_product_attribute` pa ON (p.`id_product` = pa.`id_product`) LEFT JOIN psy_product_attribute_shop product_attribute_shop ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND product_attribute_shop.`default_on` = 1) LEFT JOIN psy_stock_available stock ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(`product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop = 1 ) LEFT JOIN psy_product_lang pl ON (pl.id_product = p.id_product AND pl.id_shop = 1 AND pl.id_lang = 1) LEFT JOIN `psy_image` i ON (i.`id_product` = p.`id_product`) LEFT JOIN psy_image_shop image_shop ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1) LEFT JOIN `psy_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 1) LEFT JOIN psy_manufacturer m ON (m.id_manufacturer = p.id_manufacturer) WHERE product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") AND c.id_category = 15 AND c.active = 1 AND p.id_product IN (39,40,thousands of products ID's, 29674) GROUP BY product_shop.id_product ORDER BY cp.position asc LIMIT 48,48;
    

  • administrators

    but then with the EXPLAIN of course xD

    SELECT EXPLAIN p.*, product_shop.*, product_shop.id_category_default, pl.*, MAX(image_shop.`id_image`) id_image, il.legend, m.name manufacturer_name, MAX(pa.id_product_attribute) id_product_attribute, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity FROM `psy_category_product` cp LEFT JOIN psy_category c ON (c.id_category = cp.id_category) LEFT JOIN `psy_product` p ON p.`id_product` = cp.`id_product` INNER JOIN psy_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) LEFT JOIN `psy_product_attribute` pa ON (p.`id_product` = pa.`id_product`) LEFT JOIN psy_product_attribute_shop product_attribute_shop ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND product_attribute_shop.`default_on` = 1) LEFT JOIN psy_stock_available stock ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(`product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop = 1 ) LEFT JOIN psy_product_lang pl ON (pl.id_product = p.id_product AND pl.id_shop = 1 AND pl.id_lang = 1) LEFT JOIN `psy_image` i ON (i.`id_product` = p.`id_product`) LEFT JOIN psy_image_shop image_shop ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1) LEFT JOIN `psy_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 1) LEFT JOIN psy_manufacturer m ON (m.id_manufacturer = p.id_manufacturer) WHERE product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") AND c.id_category = 15 AND c.active = 1 AND p.id_product IN (39,40,thousands of products ID's, 29674) GROUP BY product_shop.id_product ORDER BY cp.position asc LIMIT 48,48;
    

Log in to reply
 

Looks like your connection to thirty bees forum was lost, please wait while we try to reconnect.