I'm still curious if adding an index on the parent_id field in the db will help or not. So far noone with the slowness issue has tried it and reported back.
OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter
This is running on a slowest server possible and I am testing lot of things. This is my dev site not a production one.
Extensions: Multi-vendor extension
Shipping Modules: SuperShip Pro
Multi-Vendor Marketplace:Opencartmarketplace.com
IceCat to Opencart: Import millions of IceCat products to Opencart
Xsecrets wrote:I'm still curious if adding an index on the parent_id field in the db will help or not. So far noone with the slowness issue has tried it and reported back.
I'm interesting in this also.
I recently moved to the island formally owned by Dr. Moreau and currently live there lost in a ocean of computer code with my cat Nigel... help us!!!
so give it a try and let us know if it helps.zeroevil wrote:Xsecrets wrote:I'm still curious if adding an index on the parent_id field in the db will help or not. So far noone with the slowness issue has tried it and reported back.
I'm interesting in this also.
OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter
For anyone having the super sloooow site loading time, this IS the absolute REMEDY. I was struggling with an extremely slow page load (up to 15 sec at times) in both 1.4.8 and 1.4.9. I have about 600 products in 70 categories (including nested/child cats) Tried all kinds of suggestions from the forum to no avail. Did the simple fix above and it's like night and day. My page loads are much, much faster! Soooo Thankful Q! BTW, I do not have the category mod referenced in this thread installed.Qphoria wrote:So try this
1. EDIT: catalog/model/catalog/category.php
2. FIND:3. REPLACE WITH:Code: Select all
if (!$category_data) {Code: Select all
if (!$category_data && !is_array($category_data)) {
P.S. - I'd be happy to try the index idea if someone would tell me how to do it as well.
http://www.creativesolutionscompany.com
Opencart 1.4.9.1
Global Options Mod
Excellent to hear! This change has already been made in v1.4.9.3 as wellmomtrepreneur wrote:For anyone having the super sloooow site loading time, this IS the absolute REMEDY. I was struggling with an extremely slow page load (up to 15 sec at times) in both 1.4.8 and 1.4.9. I have about 600 products in 70 categories (including nested/child cats) Tried all kinds of suggestions from the forum to no avail. Did the simple fix above and it's like night and day. My page loads are much, much faster! Soooo Thankful Q! BTW, I do not have the category mod referenced in this thread installed.Qphoria wrote:So try this
1. EDIT: catalog/model/catalog/category.php
2. FIND:3. REPLACE WITH:Code: Select all
if (!$category_data) {Code: Select all
if (!$category_data && !is_array($category_data)) {
P.S. - I'd be happy to try the index idea if someone would tell me how to do it as well.
http://www.creativesolutionscompany.com
@ momtrepreneur: I went to your site and after clicking a category, it took 22 seconds for that page to come up. After that, the page loads were normal.
-------------------------
I'm having this exact same problem running 1.4.9.3 on localhost using MAMP. I have 980 cats/sub-cats and 8679 products in the db. When I first go to my store, it takes upwards of 28 seconds to load the page. After that, pages load in about 2 seconds. Also, if I walk away from the computer for any length of time, the first page load after that is over 25 seconds again.
We cannot go live like this, so I hope we can find the problem.
Marc
BTW, I added an index to parent_id as Xsecrets asked and will report back on my findings.
-------------------------
I'm having this exact same problem running 1.4.9.3 on localhost using MAMP. I have 980 cats/sub-cats and 8679 products in the db. When I first go to my store, it takes upwards of 28 seconds to load the page. After that, pages load in about 2 seconds. Also, if I walk away from the computer for any length of time, the first page load after that is over 25 seconds again.
We cannot go live like this, so I hope we can find the problem.
Marc
BTW, I added an index to parent_id as Xsecrets asked and will report back on my findings.
OpenCart v1.4.9.4
VQMod | Categories Home | Cleaner By Default - 2 Column | Speak Good English
you may also want to checkout the other slowness thread. there are some db query tweaks I found that seem to help things.
OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter
I tried the cart section, still seemed pretty slow:
http://www.creativesolutionscompany.com/shop/
Did something change?
If related to cats, is it possible to build a static cat list for quick retrieval?
Would SmartOptimizer help?
http://forum.opencart.com/viewtopic.php ... 3&p=107777
Thanks
http://www.creativesolutionscompany.com/shop/
Did something change?
If related to cats, is it possible to build a static cat list for quick retrieval?
Would SmartOptimizer help?
http://forum.opencart.com/viewtopic.php ... 3&p=107777
Thanks
Last edited by scanreg on Sun Jan 02, 2011 9:57 am, edited 2 times in total.
Adding an index to parent_id seems to have helped a lot. Page load times have dropped from over 25 seconds down to around 6 seconds. Subsequent pages still load in about 2 seconds. Unfortunately, I forgot to see how much the db grew because of the new index. 
Using Q's Log & Analyze DB Queries mod sheds some more light on the situation. Here are the first 3 lines:
You'll notice that there isn't too much of a time lag between the first two entries. However, there's a rather long span between the 2nd and 3rd entry. What could be causing that?
Here's the last entry in the log:
In the thread I mentioned, Q said:
Marc
Using Q's Log & Analyze DB Queries mod sheds some more light on the situation. Here are the first 3 lines:
Code: Select all
--- PAGE URL: route=product/category&path=1001_2001_3002_4009 ---
[/Applications/MAMP/htdocs/_carts/opencart/index.php]
Query Time: 0.0012 :: SELECT * FROM setting
Aggregate: 0.0012
[/Applications/MAMP/htdocs/_carts/opencart/index.php]
Query Time: 0.0003 :: SELECT * FROM store WHERE url = 'http://localhost:8888/_carts/opencart/' OR url = 'http://localhost:8888/_carts/opencart/'
Aggregate: 0.0015
[/Applications/MAMP/htdocs/_carts/opencart/index.php]
Query Time: 0.0003 :: SELECT * FROM language
Aggregate: 25.1226
[/Applications/MAMP/htdocs/_carts/opencart/system/library/currency.php]
Query Time: 0.0002 :: SELECT * FROM currency
Aggregate: 25.1228Here's the last entry in the log:
Code: Select all
[/Applications/MAMP/htdocs/_carts/opencart/catalog/model/catalog/information.php]
Query Time: 0.0023 :: SELECT * FROM information i LEFT JOIN information_description id ON (i.information_id = id.information_id) LEFT JOIN information_to_store i2s ON (i.information_id = i2s.information_id) WHERE id.language_id = '1' AND i2s.store_id = '0' AND i.status = '1' AND i.sort_order <> '-1' ORDER BY i.sort_order, LCASE(id.title) ASC
Aggregate: 25.6106However, as you can see, my times are much higher than that — 25.6106 in total. Or perhaps he meant individual query times? If so, what is the aggregate number?Times should be low in most cases. No more than 0.0050 I would say and even that should only be for very large queries.
Marc
OpenCart v1.4.9.4
VQMod | Categories Home | Cleaner By Default - 2 Column | Speak Good English
I think "aggregate number" is buggered.
I've had any where between 50 and 250 ...
I've had any where between 50 and 250 ...
930sc ... because it is fun!
well the aggregate time is just that an aggregate time not an aggregate query time. If you'll notice your first three queries do not add up to the aggregate time. There is something in the index that is eating up time that is not a database query. At some point I'm going to have to get my profiler setup and working so I can find out what it is. It's something in the php not a db query.
OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter
For me it helped a lot to disable the log component form index.php
Find and comment the 2 lines after //LOG (around lines 65-67 ), after that comment the 3 lines around the 95-98 lines and this should disable the error log file. With this modifications the response time from the server seems a lot quicker for me and the cart seems faster.
Find and comment the 2 lines after //LOG (around lines 65-67 ), after that comment the 3 lines around the 95-98 lines and this should disable the error log file. With this modifications the response time from the server seems a lot quicker for me and the cart seems faster.
Hi Q
I have had a lookin in my file and all I have is this.
I cant find how to change it on 1.4.8b
Can you help.
Many Thanks
<?php
class ModelCatalogCategory extends Model {
public function getCategory($category_id) {
$query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.category_id = '" . (int)$category_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
return $query->row;
}
public function getCategories($parent_id = 0) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.parent_id = '" . (int)$parent_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "' ORDER BY c.sort_order, cd.name");
return $query->rows;
}
public function getTotalCategoriesByCategoryId($parent_id = 0) {
$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.parent_id = '" . (int)$parent_id . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
return $query->row['total'];
}
public function fetchCategories(){
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "category c
LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id)
LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id)
WHERE cd.language_id = '" . (int)$this->config->get('config_language_id') . "'
AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "'
ORDER BY c.sort_order, cd.name");
return $query->rows;
}
public function fetchCategory($category_id = 0){
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "category c
LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id)
LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id)
WHERE c.parent_id = '" . (int)$category_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
return $query->rows;
}
}
?>
I have had a lookin in my file and all I have is this.
I cant find how to change it on 1.4.8b
Can you help.
Many Thanks
<?php
class ModelCatalogCategory extends Model {
public function getCategory($category_id) {
$query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.category_id = '" . (int)$category_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
return $query->row;
}
public function getCategories($parent_id = 0) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.parent_id = '" . (int)$parent_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "' ORDER BY c.sort_order, cd.name");
return $query->rows;
}
public function getTotalCategoriesByCategoryId($parent_id = 0) {
$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.parent_id = '" . (int)$parent_id . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
return $query->row['total'];
}
public function fetchCategories(){
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "category c
LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id)
LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id)
WHERE cd.language_id = '" . (int)$this->config->get('config_language_id') . "'
AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "'
ORDER BY c.sort_order, cd.name");
return $query->rows;
}
public function fetchCategory($category_id = 0){
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "category c
LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id)
LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id)
WHERE c.parent_id = '" . (int)$category_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
return $query->rows;
}
}
?>
Qphoria wrote:Actually I'm wrong. The caching IS on all categories. But I think there might be an issue with empty categories. Since the cache returns an empty array for categories with no subs, it requeries the database as it reads "empty array" and "false variable" the same.
So try this
1. EDIT: catalog/model/catalog/category.php
2. FIND:3. REPLACE WITH:Code: Select all
if (!$category_data) {Code: Select all
if (!$category_data && !is_array($category_data)) {
Wow !! This works just great !! I was disappointed because after encoding 2500 products in my store, my website had become dreadfully slow : around 15 seconds to load a single page. I tried everything to speed it up, without success. But this fix is amazing ! Now the website is loading instantly from one page to another.cartpro wrote:First thing I would do is to disable the category module and comment out the following line in common/header.php (around line 118) to check if it is really category issue.
I have loaded my site with 200+ categories and 11,300+ product and it takes 10-15seconds. Check out at http://www.sansdirect.com and see if you see the same response.Code: Select all
$this->data['categories'] = $this->getCategories(0);
Also, check your error log for any script errors and warnings. This will reduce the response time too.
Thanks again !
This fix cured my slow site which has a lot of categories. Thanks!momtrepreneur wrote:For anyone having the super sloooow site loading time, this IS the absolute REMEDY. I was struggling with an extremely slow page load (up to 15 sec at times) in both 1.4.8 and 1.4.9. I have about 600 products in 70 categories (including nested/child cats) Tried all kinds of suggestions from the forum to no avail. Did the simple fix above and it's like night and day. My page loads are much, much faster! Soooo Thankful Q! BTW, I do not have the category mod referenced in this thread installed.Qphoria wrote:So try this
1. EDIT: catalog/model/catalog/category.php
2. FIND:3. REPLACE WITH:Code: Select all
if (!$category_data) {Code: Select all
if (!$category_data && !is_array($category_data)) {
P.S. - I'd be happy to try the index idea if someone would tell me how to do it as well.
http://www.creativesolutionscompany.com
that's really strange as the manufacturers is a straight query not a recursive one like the categories.
OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter
Website having too slow is a major issue. Its happens when our page redirects form user to server again and again. Another thing is if you have large number of images then site takes long time to display the full content of the page. So try to solve this problems first and change your code.
Who is online
Users browsing this forum: No registered users and 110 guests
