I have discovered very slow mysql query performance for
Code: Select all
model_catalog_product->getTotalProducts()After all products were imported, store started to work really slow.
I has a lot of categories and products on the first page, and in the top menu. So - every request asks for "total products in category".
I installed small debug tool to collect all sql queries (http://forum.opencart.com/viewtopic.php?t=27918).
Collected a list of ALL queries executed on the first page, and then - just did simple execution from mysql command line.
The problem is with /model/catalog/product.php. $sql starts at line 472 (v.1.5.6.1),
but the query all together for counting products in category, looks like:
Code: Select all
SELECT COUNT(DISTINCT p.product_id) AS total FROM category_path cp LEFT JOIN product_to_category p2c ON (cp.category_id = p2c.category_id) LEFT JOIN product p ON (p2c.product_id = p.product_id) LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '3' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND cp.path_id = '26';
Code: Select all
AND p.date_available <= NOW()it takes 3 seconds to complete the query from command line (query executes ~86 times every page load).
And much more - the first lines executes faster, but as seconds changes, then You are comparing with different time already, so mysql must scan table again, I suppose.
FIX idea, Whad helped for me and what I did:
1) created index in table product for field "date_available", but it did not helped;
2) replaced in sql statement "now()" with php variable
Code: Select all
p.date_available <= '".date('Y-m-d')."'
This fix was HUGE improvment for my store.
I reduced load time from ~4 seconds to almost instant (<1s).
Guys, if You are experiencing the same problem, I can create a simple VQMOD (I will for my self anyway).
Just, please, let me know, if I am wrong
or I have dubplicated post.I am looking to replace other comparing functions with now()
I really don't know - is it connected with some server or shared hosting configuration, but ... - date() WORKS perfectly, ythat is enough for me
