Opencart+Journal3 optimization (high-traffic store with 100k+ products)
Budget: €750 – €1,500 EUR
Hello
We are observing that Opencart (3.0.3.7) and/or Journal3 theme is very inefficient in case of big stores with multiple products.
We've installed caching module, it helped a lot, but we still observe slow queries that take 2s+
For example this is one of our search queries:
SELECT SQL_CALC_FOUND_ROWS DISTINCT p2c.category_id, COUNT(p.product_id) as product_count FROM ( SELECT p.product_id, (SELECT AVG(rating) AS total FROM octest_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT price FROM octest_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM octest_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, pd.name, p.weight, p.length, p.width, p.height, p.points, (SELECT SUM(op.quantity) AS total FROM octest_order_product op LEFT JOIN `octest_order` o ON (op.order_id = o.order_id) WHERE o.order_status_id > '0' AND op.product_id = p.product_id GROUP BY op.product_id ORDER BY total DESC) AS sold_count, (SELECT COUNT(review_id) AS total FROM octest_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS review_count FROM octest_product p LEFT JOIN octest_product_attribute pa ON (p.product_id = pa.product_id) LEFT JOIN octest_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN octest_product_description_seo pds ON (p.product_id = pds.product_id AND pds.language_id = '1') LEFT JOIN octest_product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND ( (LCASE(pd.name) LIKE '%i5%' OR LCASE(p.model) LIKE '%i5%' OR LCASE(p.sku) LIKE '%i5%' OR LCASE(p.upc) LIKE '%i5%' OR LCASE(p.ean) LIKE '%i5%' OR LCASE(p.mpn) LIKE '%i5%' OR LCASE(pa.text) LIKE '%i5%') AND (LCASE(pd.name) LIKE '%10400f%' OR LCASE(p.model) LIKE '%10400f%' OR LCASE(p.sku) LIKE '%10400f%' OR LCASE(p.upc) LIKE '%10400f%' OR LCASE(p.ean) LIKE '%10400f%' OR LCASE(p.mpn) LIKE '%10400f%' OR LCASE(pa.text) LIKE '%10400f%') OR pd.tag LIKE '%i5%' AND pd.tag LIKE '%10400f%') GROUP BY p.product_id) p LEFT JOIN octest_product_to_category p2c ON (p.product_id = p2c.product_id) GROUP BY p2c.category_id
We think that because of this, site feels sluggish whenever it needs something from SQL, also search queries takes very long time
We are looking for a developer who would optimize those queries, preferably with as small caching as possible - store automatically imports/updates products from 6 different distributor APIs.
Experts only please. We would like to see results in 1-1.5 weeks.
We are observing that Opencart (3.0.3.7) and/or Journal3 theme is very inefficient in case of big stores with multiple products.
We've installed caching module, it helped a lot, but we still observe slow queries that take 2s+
For example this is one of our search queries:
SELECT SQL_CALC_FOUND_ROWS DISTINCT p2c.category_id, COUNT(p.product_id) as product_count FROM ( SELECT p.product_id, (SELECT AVG(rating) AS total FROM octest_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT price FROM octest_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM octest_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, pd.name, p.weight, p.length, p.width, p.height, p.points, (SELECT SUM(op.quantity) AS total FROM octest_order_product op LEFT JOIN `octest_order` o ON (op.order_id = o.order_id) WHERE o.order_status_id > '0' AND op.product_id = p.product_id GROUP BY op.product_id ORDER BY total DESC) AS sold_count, (SELECT COUNT(review_id) AS total FROM octest_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS review_count FROM octest_product p LEFT JOIN octest_product_attribute pa ON (p.product_id = pa.product_id) LEFT JOIN octest_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN octest_product_description_seo pds ON (p.product_id = pds.product_id AND pds.language_id = '1') LEFT JOIN octest_product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND ( (LCASE(pd.name) LIKE '%i5%' OR LCASE(p.model) LIKE '%i5%' OR LCASE(p.sku) LIKE '%i5%' OR LCASE(p.upc) LIKE '%i5%' OR LCASE(p.ean) LIKE '%i5%' OR LCASE(p.mpn) LIKE '%i5%' OR LCASE(pa.text) LIKE '%i5%') AND (LCASE(pd.name) LIKE '%10400f%' OR LCASE(p.model) LIKE '%10400f%' OR LCASE(p.sku) LIKE '%10400f%' OR LCASE(p.upc) LIKE '%10400f%' OR LCASE(p.ean) LIKE '%10400f%' OR LCASE(p.mpn) LIKE '%10400f%' OR LCASE(pa.text) LIKE '%10400f%') OR pd.tag LIKE '%i5%' AND pd.tag LIKE '%10400f%') GROUP BY p.product_id) p LEFT JOIN octest_product_to_category p2c ON (p.product_id = p2c.product_id) GROUP BY p2c.category_id
We think that because of this, site feels sluggish whenever it needs something from SQL, also search queries takes very long time
We are looking for a developer who would optimize those queries, preferably with as small caching as possible - store automatically imports/updates products from 6 different distributor APIs.
Experts only please. We would like to see results in 1-1.5 weeks.