[SOLVED] Page Load very Slow

Hi

we have website http://69.85.93.10/~flagsand/index.php? ... th=176_195 which loads very slowly.

i found a query which i think reason of slowness . i have more than 10k products

$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$customer_group_id . "' 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 " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$customer_group_id . "' 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, (SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$customer_group_id . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");

this query has lots of join. due to this it loads slowly? i checked "show processlist" sql command on mysql server.

please advise me about this.

thanks
ashu

10,000 products really isn't that many. I'm not sure what you're trying to achieve here but the query looks way too complicated. There are probably some parts of it that are extremely expensive in terms of hitting mysql.

You need to dump this query into a query tool and run it ... find out how long it's actually taking to run.

Quickly scanning the query I notice things like calculating the average rating. Pieces of your query like can all be pre-built. Mysql has a scheduler. Data like average rating don't need to be real-time. You can update them every minute, hour etc....as required for your site. Build a query for that... add another field in the product table for average rating. Calculate it for every product as required(....like 3 every morning) Things like this take a huge load of these queries. Your need to break it down AND make sure you have all the indexes you need on your tables.

upl8t wrote:10,000 products really isn't that many. I'm not sure what you're trying to achieve here but the query looks way too complicated. There are probably some parts of it that are extremely expensive in terms of hitting mysql.

You need to dump this query into a query tool and run it ... find out how long it's actually taking to run.

Quickly scanning the query I notice things like calculating the average rating. Pieces of your query like can all be pre-built. Mysql has a scheduler. Data like average rating don't need to be real-time. You can update them every minute, hour etc....as required for your site. Build a query for that... add another field in the product table for average rating. Calculate it for every product as required(....like 3 every morning) Things like this take a huge load of these queries. Your need to break it down AND make sure you have all the indexes you need on your tables.

I think OP is saying that this query is what OpenCart is running, out of the box. It's untouched. Why isn't it optimized?

I agree, 10,000 products is not that many in the grand scheme of things especially if you are doing "your part" and using at the very least a VPS or Dedicated rack to host your shop. While yes, SELECT DISTINCT * does take a long time to complete,
I would recommend cutting down on how many times you query your database by using page cache:
http://www.opencart.com/index.php?route ... on_id=3477

upl8t wrote:10,000 products really isn't that many. I'm not sure what you're trying to achieve here but the query looks way too complicated. There are probably some parts of it that are extremely expensive in terms of hitting mysql.

You need to dump this query into a query tool and run it ... find out how long it's actually taking to run.

Quickly scanning the query I notice things like calculating the average rating. Pieces of your query like can all be pre-built. Mysql has a scheduler. Data like average rating don't need to be real-time. You can update them every minute, hour etc....as required for your site. Build a query for that... add another field in the product table for average rating. Calculate it for every product as required(....like 3 every morning) Things like this take a huge load of these queries. Your need to break it down AND make sure you have all the indexes you need on your tables.

Hi
Above mention query used in the function getProduct.

Now Opencart call this method in foreach loop means every looping this query runs and load mysql server.

foreach ($query->rows as $result) {
$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
}
it also create load on mysql ? am I right?

please advise me?

thanks
ashu

avvici wrote:I agree, 10,000 products is not that many in the grand scheme of things especially if you are doing "your part" and using at the very least a VPS or Dedicated rack to host your shop. While yes, SELECT DISTINCT * does take a long time to complete,
I would recommend cutting down on how many times you query your database by using page cache:
http://www.opencart.com/index.php?route ... on_id=3477

Hi

i already search page caching extension of opencart which are paid.

http://www.opencart.com/index.php?route ... ch=%20slow

http://www.opencart.com/index.php?route ... ch=%20slow

Means we have to purchase this extension for reduce to load on mysql server.not any other option?

please advise me

thanks
ashu

Does anyone have any real assistance besides directing us to some expensive Module to make our site run as any other website should? It takes 15-20 seconds to load the home page. We can't even browse our products. We only have around 8,000 products. We turned off category count.

This is urgent now.

bluby wrote:Does anyone have any real assistance besides directing us to some expensive Module to make our site run as any other website should? It takes 15-20 seconds to load the home page. We can't even browse our products. We only have around 8,000 products. We turned off category count.

This is urgent now.

That isn't just "some expensive module" especially if you are using crappy hosting. Are you using shared hosting? Also why not send us a link?

Again, we did offer you "real" assistance. Either take it or leave it.

hi

So you say this not your fault ..but We are running over Dedicated server and have Good Memory size..and one thing 8k product does not need dedicated server for page speed.. something in opencart coding creates page load slow..

so please advise us where is its in opencart

thanks
ashu

avvici wrote:

bluby wrote:Does anyone have any real assistance besides directing us to some expensive Module to make our site run as any other website should? It takes 15-20 seconds to load the home page. We can't even browse our products. We only have around 8,000 products. We turned off category count.

This is urgent now.

That isn't just "some expensive module" especially if you are using crappy hosting. Are you using shared hosting? Also why not send us a link?

Again, we did offer you "real" assistance. Either take it or leave it.

A store with a bit under 10k products on a VPS completed the query in 0.12 seconds. Not brilliantly fast but a far cry from 15-20 seconds.

Look I had a website of hotels searching which is more than 1.5lakh hotel record..which running so far good on share server..
you can check here hotels.laymansystems.com
man it is not problem of server..thing is opencart coding ..it using so many joiins and within loop in query ..

rph wrote:A store with a bit under 10k products on a VPS completed the query in 0.12 seconds. Not brilliantly fast but a far cry from 15-20 seconds.

Before this topic is locked due to an obvious lack of information from the beginning how about tell us the following information:

-Your Open Cart Version
-Bandwidth and storage for your dedicated rack
-Your Hosting Provider
-A Link to your store

Ty.

avvici wrote:Before this topic is locked due to an obvious lack of information from the beginning how about tell us the following information:

-Your Open Cart Version
-Bandwidth and storage for your dedicated rack
-Your Hosting Provider
-A Link to your store

Ty.

Avvici, Thanks for your attention on this.

- Open Cart version v1.5.3.1
- unlimited, blazing fast, admin is super fast. Home page is slow as a pig.
- Hafer LLC (dedicated box)
- http://69.85.93.10/~flagsand/

Looking forward to your response!

Thanks!

The issue is likely the huge number of categories you have, not the number of products.

rph wrote:The issue is likely the huge number of categories you have, not the number of products.

Why would that be the case? Categories should not bring down a site.

I have another site with a lot of categories and its blazing fast

Yeah that is horribly slow. rph is correct. Besides if you have another site that is fast with same number of categories, whos to say that site isn't doing a better job caching?

Do this:
Start by disabling the category count in administration. This will help out a lot.

Then, purchase the page cache module we mentioned. With tons of categories comes responsibility. Purchasing the module is worth it.

avvici wrote:Yeah that is horribly slow. rph is correct. Besides if you have another site that is fast with same number of categories, whos to say that site isn't doing a better job caching?

Do this:
Start by disabling the category count in administration. This will help out a lot.

Then, purchase the page cache module we mentioned. With tons of categories comes responsibility. Purchasing the module is worth it.

Category count already disabled since day 1. It has alot of categories but not as many sub cats. Client cannot afford to pay for the module. Am I the only one running a big store with opencart?? This seems silly.

avvici wrote:Yeah that is horribly slow. rph is correct. Besides if you have another site that is fast with same number of categories, whos to say that site isn't doing a better job caching?

Do this:
Start by disabling the category count in administration. This will help out a lot.

Then, purchase the page cache module we mentioned. With tons of categories comes responsibility. Purchasing the module is worth it.

And are you telling me that if I remove all the SUBCATS the site will perform blazing fast as it should? We only have about 8,000 of the 50,000 products added right now. (might as well ask, If I delete subcats do the products in them get deleted too?)

Don't go and start deleting anything. And no, the majority of people that use open cart do not have that amount of categories and sub c's. Those that do use procedures that enhance and maintain that type of query strain on the database. How about contact me via skype and we can work on getting it faster there.

My Skype: Involution Media