OpenCart Community

Posted: **Mon Dec 06, 2010 2:52 am**

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.

Posted: **Mon Dec 06, 2010 4:13 am**

This is running on a slowest server possible and I am testing lot of things. This is my dev site not a production one.

Posted: **Tue Dec 07, 2010 3:36 am**

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.

Posted: **Tue Dec 07, 2010 4:42 am**

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.

so give it a try and let us know if it helps.

Posted: **Wed Dec 22, 2010 6:02 am**

Qphoria wrote:So try this

1. EDIT: catalog/model/catalog/category.php

2. FIND:
Code: Select all
if (!$category_data) { 
3. REPLACE WITH:
Code: Select all
if (!$category_data && !is_array($category_data)) { 

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.

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

Posted: **Wed Dec 22, 2010 6:08 am**

momtrepreneur wrote:
Qphoria wrote:So try this

1. EDIT: catalog/model/catalog/category.php

2. FIND:
Code: Select all
if (!$category_data) { 
3. REPLACE WITH:
Code: Select all
if (!$category_data && !is_array($category_data)) { 
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.

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

Excellent to hear! This change has already been made in v1.4.9.3 as well

Posted: **Sun Jan 02, 2011 4:12 am**

@ 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.

Posted: **Sun Jan 02, 2011 6:20 am**

you may also want to checkout the other slowness thread. there are some db query tweaks I found that seem to help things.

Posted: **Sun Jan 02, 2011 9:30 am**

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

Posted: **Sun Jan 02, 2011 9:32 am**

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:

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.1228

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:

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.6106

In the thread I mentioned, Q said:

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.

However, 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?

Marc

Posted: **Sun Jan 02, 2011 9:57 am**

I think "aggregate number" is buggered.

I've had any where between 50 and 250 ...

Posted: **Sun Jan 02, 2011 11:34 am**

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.

Posted: **Sun Jan 02, 2011 6:42 pm**

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.

Posted: **Tue Feb 22, 2011 7:23 am**

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;
}
}
?>

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:
Code: Select all
if (!$category_data) { 
3. REPLACE WITH:
Code: Select all
if (!$category_data && !is_array($category_data)) { 

Posted: **Wed Mar 16, 2011 8:58 pm**

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.
Code: Select all
$this->data['categories'] = $this->getCategories(0);
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.

Also, check your error log for any script errors and warnings. This will reduce the response time too.

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.

Thanks again !

Posted: **Fri Apr 22, 2011 4:38 am**

momtrepreneur wrote:
Qphoria wrote:So try this

1. EDIT: catalog/model/catalog/category.php

2. FIND:
Code: Select all
if (!$category_data) { 
3. REPLACE WITH:
Code: Select all
if (!$category_data && !is_array($category_data)) { 
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.

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

This fix cured my slow site which has a lot of categories. Thanks!

Posted: **Thu May 12, 2011 4:10 am**

If you have a bunch of manufacturers, like in my case artists since I sell CD's, then turn off the manufacturer module on and your speed will increase exponentially. I went from 30-40 second page loads to 4-6 second page loads.

Devin

Posted: **Thu May 12, 2011 11:04 am**

that's really strange as the manufacturers is a straight query not a recursive one like the categories.

Posted: **Fri May 13, 2011 3:16 am**

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.

OpenCart Community

Website is too slow

Re: Website is too slow

Re: Website is too slow

Re: Website is too slow

Re: Website is too slow

Re: Website is too slow

Re: Website is too slow

Re: Website is too slow

Re: Website is too slow

Re: Website is too slow

Re: Website is too slow

Re: Website is too slow

Re: Website is too slow

Re: Website is too slow

Re: Website is too slow

Re: Website is too slow

Re: Website is too slow

Re: Website is too slow

Re: Website is too slow

Re: Website is too slow