Hi, we have recently upgraded our opencart store to the highest version below opencart2.
The site is great however, the page load speed is poor at over 35s per page. Once we click on every category and product the page loads at 1.2s per page. We have found that the page load is in a left loop and trying to load the page content several times before it finally loads.
Could anyone tell us what we might be able to do to cure the page load speed. The site has 80 categories and sub categories and 4000 products. Our hosting guys have spotted an issue below:
We've narrowed this down to an issue with the way that your site is interfacing with the database. Every page load is generating a large number of database queries (we've watched these flow through the database server), and it's not until every single one of these completes that the page renders.
As you load a page, we see many many queries like this - these are just a few for reference:
---
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 <= NOW() AND p2s.store_id = '0' AND cp.path_id = '127'
---
it appears that the load time is being caused by some looping query in the code that appears to be performing left joins over and over again before the page is produced for the visitor.
We are still getting to grips with opencart and are struggling to find a solution to this load speed issue. It may not be the left join as above but if anyone could suggest a cure we would be eternally grateful.
You might benefit from creating database indexes. We have commercial extension that does that (Premium DB Indexes), and there are also few free ones available on github and forums.
___________________________________________________________
SuperDruid Extensions
http://cartadvisor.com/blog/2013/11/05/ ... art-store/ -tried and 100% works, went from 23/33 score in page speed to 60-70
http://octurbo.com/a-page-cache-for-opencart/-this could also help, untested yet
.
http://octurbo.com/a-page-cache-for-opencart/-this could also help, untested yet
.
I placed a few links about this here on the main Page::
http://www.bigmax.ch/shop/
and here, you find infos as well:
http://forum.opencart.com/viewtopic.php?t=128274
http://forum.opencart.com/viewtopic.php ... 42#p514037
Good Luck
Ernie
http://www.bigmax.ch/shop/
and here, you find infos as well:
http://forum.opencart.com/viewtopic.php?t=128274
http://forum.opencart.com/viewtopic.php ... 42#p514037
Good Luck
Ernie
My Github OC Site: https://github.com/IP-CAM
5'600 + FREE OC Extensions, on the World's largest private Github OC Repository Archive Site.
Or just add correct index to mysql table:
And then this index is being used:
Code: Select all
ALTER TABLE `oc_category_path` ADD INDEX ( `path_id` ) ;Code: Select all
EXPLAIN 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 = '2'
AND p.status = '1'
AND p.date_available <= '2015-04-21 15:19:00'
AND p2s.store_id = '0'
AND cp.path_id = '99'SIMPLE cp ref PRIMARY,path_id path_id 4 const 2
1 SIMPLE p2s index PRIMARY PRIMARY 8 NULL 870 Using where; Using index; Using join buffer
1 SIMPLE pd eq_ref PRIMARY PRIMARY 8 krabolov_335.p2s.product_id,const 1 Using where; Using index
1 SIMPLE p eq_ref PRIMARY PRIMARY 4 krabolov_335.pd.product_id 1 Using where
1 SIMPLE p2c eq_ref PRIMARY PRIMARY 8 krabolov_335.p.product_id,krabolov_335.cp.category... 1 Using where; Using index
Or add caching of query :
catalog/model/catalog/product.php line 555 or 600
catalog/model/catalog/product.php line 555 or 600
Code: Select all
$key = sha1($sql);
$value_in_cache = false;
$value = apc_fetch($key, $value_in_cache);
if(!$value_in_cache) {
// old code
$query = $this->db->query($sql);
$result = $query->row['total'];
// end old code
apc_store($key, $result, 6400);
} else { $result = $value;}
// return $query->row['total'];
return $result;V.1.5.6.5_rc:
Turned off my Numbers, usually displaying the Sub Cat Counting as well as the PageBreak Infos at the Bottom.
Therefore, by Use of my old Product Count Cache-Mod again, the Sub Category Counting works again:
Just to mention it!
Ernie
hitline.info/shop/
Turned off my Numbers, usually displaying the Sub Cat Counting as well as the PageBreak Infos at the Bottom.
Code: Select all
// Product Count Cache
$key = sha1($sql);
$value_in_cache = false;
$value = apc_fetch($key, $value_in_cache);
if(!$value_in_cache) {
apc_store($key, $result, 6400);
} else { $result = $value;}
return $result;
}Code: Select all
// Product Count Cache
$cacheid='product.gettotalproducts.'.md5($sql).(int)$customer_group_id;
$total=$this->cache->get($cacheid);
if ($total === null ) {
$query = $this->db->query($sql);
$total = $query->row['total'];
$this->cache->set($cacheid,$total);
}
return $total;
}Ernie
hitline.info/shop/
My Github OC Site: https://github.com/IP-CAM
5'600 + FREE OC Extensions, on the World's largest private Github OC Repository Archive Site.
Who is online
Users browsing this forum: No registered users and 56 guests
