With a large number of goods, OpenCart dies, ceases to display the interface altogether. It turned out that one of the slowest queries (but frequent) is

SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_category_path cp LEFT JOIN oc_product_to_category p2c ON (cp.category_id = p2c.category_id) LEFT JOIN oc_product p ON (p2c.product_id = p.product_id) LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= '2015-05-21 12:58:00' AND p2s.store_id = '0' AND cp.path_id = '823';

EXPLAIN on it shows indexes of different sizes (key_len), although in all tables identifiers of type int (11). Why is this happening and how to treat it? If sclerosis does not change me, in terms of performance, this discrepancy is bad for me.

Screen profiling
(source: catalogloader.com )

  • And it is possible to ask the scheme with these tables and these columns? Something, it seems to me, you are doing the wrong table in the query of the main (left). Although, maybe I am mistaken: it is difficult without a scheme. - BuilderC
  • Well, not I do it, this is a standard OpenCart database. Moreover, it is not at all clear why on the title page to bypass ALL categories by the piece, for each performing this query (and it lasts 2.5-5 seconds) - QuAzI

0