Post by rmichel » Thu Mar 14, 2019 2:16 am

I am not a programer and do need someones help in solving this issue please. sANDY

I have a really big problem with slowness of my website with couple of thousand of products that I have. Just a couple of weeks before, was working great, and now it has slowed down my site. Note: My admin section does have Desactivated the Page Count Down Option.

The reason why I know is a problem with PAGE COUNT, its because I had my hosting provider ran diagnost and they replied back with the issue that Im posting in the section below. After copying and pasting down in google whats below that my hosting provider sent me, it all came back to PAGE COUNT problem.

I had a module installed a last week, which fixed the problem, but the module only was supported for previous opencart versions what I have 2.1.01, so it only worked for a few days and stopped working.


Below, Im posting the PAGE COUNT issue that my hosting provider sent me:

"The issue is an unseen coding problem. When the site is loading, it runs the following query, which takes several seconds to complete before the site finally loads:

Long Query:

| 44751 | computo_mex | localhost | computo_mex | Query | 0 | Sending data | 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 = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND cp.path_id = '36646' |


I ran a describe on this query and it does iterate over 65,000 rows:

Long Query Describe:

mysql> DESCRIBE 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 = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND cp.path_id = '36646';
+----+-------------+-------+--------+---------------------+---------+---------+-----------------------------------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------+---------+---------+-----------------------------------+-------+--------------------------+
| 1 | SIMPLE | p2c | index | PRIMARY,category_id | PRIMARY | 8 | NULL | 65316 | Using where; Using index |
| 1 | SIMPLE | cp | eq_ref | PRIMARY | PRIMARY | 8 | computo_mex.p2c.category_id,const | 1 | Using index |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | computo_mex.p2c.product_id | 1 | Using where |
| 1 | SIMPLE | p2s | eq_ref | PRIMARY | PRIMARY | 8 | computo_mex.p2c.product_id,const | 1 | Using index |
| 1 | SIMPLE | pd | eq_ref | PRIMARY | PRIMARY | 8 | computo_mex.p2c.product_id,const | 1 | Using index |
+----+-------------+-------+--------+---------------------+---------+---------+-----------------------------------+-------+--------------------------+
5 rows in set (0.01 sec

Newbie

Posts

Joined
Wed Jul 26, 2017 2:24 am

Post by IP_CAM » Thu Mar 14, 2019 9:19 am

Well, you could try this free Extension:
Database Cache
https://www.opencart.com/index.php?rout ... n_id=35767
or this one:
DB Cache
https://www.opencart.com/index.php?rout ... n_id=25388
and Mods like this:
fasterfox for opencart - speed web pages
https://www.opencart.com/index.php?rout ... n_id=24978
and/or this one, to improve performance:
SEO Url Cache ( Category + Product) Faster
https://www.opencart.com/index.php?rout ... n_id=27933
I never tested those, but they might help 'globally', to speed up things.
---
And/or contact Weismannweb, he might have an OC v.2.1.x Copy, or then,
for a 2-Cent Dontation per Product, you offer in your Shop ::) , he would
probably be willing to create a matching one. And this extension would
solve your Product Counting Problems for good, but it exists only for
OC v.1.5.x and v.3.x versions, as it comes. It's a top-notch Solution for such:
cache_category_data.xml
https://gist.github.com/weismannweb/8ad ... 8d751281b2
Good Luck! ;)
Ernie

I'm no longer active at the OC Forum. To reach me, contact: jti@jacob.ch
A Demoversion of my free Opencart LIGHT v.1.5.6.5_rc Software
can be seen in Action here: http://www.bigmax.ch
---
930+ FREE OC Extension-Repositories - from OC v.1.5.x up,
on the world's largest OC-related Github Site: https://github.com/IP-CAM
---
Image


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by thekrotek » Thu Mar 14, 2019 4:11 pm

You need to index your database. OpenCart lacks indexing and some queries are indeed start to work slow, when you have a lot of products or categories.

Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am


Post by IP_CAM » Thu Mar 14, 2019 9:16 pm

OpenCart Database Indexer, the famous Atomix TURBO Full
Indexer Version should still be working in OC 2 Versions as well:
https://github.com/IP-CAM/opencart-turbo
https://github.com/IP-CAM/Opencart-Index

I'm no longer active at the OC Forum. To reach me, contact: jti@jacob.ch
A Demoversion of my free Opencart LIGHT v.1.5.6.5_rc Software
can be seen in Action here: http://www.bigmax.ch
---
930+ FREE OC Extension-Repositories - from OC v.1.5.x up,
on the world's largest OC-related Github Site: https://github.com/IP-CAM
---
Image


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by rmichel » Fri Mar 15, 2019 11:44 am

Thank you everyone for the replies. IP CAM question for you, in regards to the Update turbo.php and Readme.md files, where do I put them to get my problem fixed of slowness? Can you help me answering that.

Newbie

Posts

Joined
Wed Jul 26, 2017 2:24 am

Post by IP_CAM » Fri Mar 15, 2019 12:06 pm

Well, readme files are for you to read, but should not be placed on servers.
Just place the turbo.php File into the SHOP ROOT Main Section, Then, call
it's default filename http: // yoursiteshop / turbo.php. Or better change
it's name to something very secret, if you plan to leave the file on the server. :D
But it could also be placed in the Admin Root Section, if this looks more pro! :laugh:
It's one of the best 1 File DB Improvement Tools ever created for OC.
Unfortunately, OC lost that famous Contributor, due to a simple Dispute ... :'(
Good Luck! ;)
Ernie
---
Image Link:
download/file.php?mode=view&id=36645

Attachments

atomix_opencart_db_indexer_db_engine_changer.jpg

atomix_opencart_db_indexer_db_engine_changer.jpg (337.58 KiB) Viewed 185 times


I'm no longer active at the OC Forum. To reach me, contact: jti@jacob.ch
A Demoversion of my free Opencart LIGHT v.1.5.6.5_rc Software
can be seen in Action here: http://www.bigmax.ch
---
930+ FREE OC Extension-Repositories - from OC v.1.5.x up,
on the world's largest OC-related Github Site: https://github.com/IP-CAM
---
Image


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland
Who is online

Users browsing this forum: No registered users and 50 guests