Post by arnisraido » Sun Jan 19, 2014 11:27 pm

Hi guys!

I have discovered very slow mysql query performance for

Code: Select all

model_catalog_product->getTotalProducts()
I have a store with ~2000 products, with 111 categories.
After all products were imported, store started to work really slow.

I has a lot of categories and products on the first page, and in the top menu. So - every request asks for "total products in category".

I installed small debug tool to collect all sql queries (http://forum.opencart.com/viewtopic.php?t=27918).
Collected a list of ALL queries executed on the first page, and then - just did simple execution from mysql command line.

The problem is with /model/catalog/product.php. $sql starts at line 472 (v.1.5.6.1),
but the query all together for counting products in category, looks like:

Code: Select all

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 = '3' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND cp.path_id = '26';
And there is a problem with

Code: Select all

AND p.date_available <= NOW()
I really do not know why - and probably, this can depend from different servers and configuration, I would be happy if someone could test it with ~2000 products and ~100 categories on Your server:

it takes 3 seconds to complete the query from command line (query executes ~86 times every page load).

And much more - the first lines executes faster, but as seconds changes, then You are comparing with different time already, so mysql must scan table again, I suppose.


FIX idea, Whad helped for me and what I did:
1) created index in table product for field "date_available", but it did not helped;
2) replaced in sql statement "now()" with php variable

Code: Select all

p.date_available <= '".date('Y-m-d')."'
There is no need to use "now()", if date is stored as DATE, not time, seconds.

This fix was HUGE improvment for my store.

I reduced load time from ~4 seconds to almost instant (<1s).

Guys, if You are experiencing the same problem, I can create a simple VQMOD (I will for my self anyway).

Just, please, let me know, if I am wrong :) or I have dubplicated post.

I am looking to replace other comparing functions with now()

I really don't know - is it connected with some server or shared hosting configuration, but ... - date() WORKS perfectly, ythat is enough for me :)

Newbie

Posts

Joined
Mon Aug 27, 2012 11:24 pm

Post by IP_CAM » Wed Jul 16, 2014 1:22 am

arnisraido wrote:Hi guys!
$sql starts at line 472 (v.1.5.6.1),
... replaced in sql statement "now()" with php variable

Code: Select all

p.date_available <= '".date('Y-m-d')."'
There is no need to use "now()", if date is stored as DATE, not time, seconds.
Hello, I am very interested to find out, I have presently 12'000 active Products online.
Unfortunately, in my OC 1.5.6.1-4 model/catalog/product.php, I find many entries such as:

Code: Select all

p.date_available <=
and I don't know wich one to change to make the test.

I enclose my query-result page as well as the product.php file in a zip-file.

I already have made several Mock-Up's, and the system works well so far,
but if anything can be done to make it better, I like to do it.

Thanks for a reply.

Ernie

Please don't send me OC Forum Personal Messages, just contact: jti@jacob.ch
---
OC 1.5.6.5 LIGHT Test Site: http://www.bigmax.ch/shop/
OC 1.5.6.5 V-PRO Test Site: http://www.openshop.li/shop/
My Github OC Site: https://github.com/IP-CAM
2'800+ FREE OC Extensions on the World's largest Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

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

Post by Cue4cheap » Thu Jul 24, 2014 8:46 am

Does this have any use if you turn off the product count?

Mike

cue4cheap not cheap quality


Active Member

Posts

Joined
Fri Sep 20, 2013 4:45 am

Post by IP_CAM » Thu Jul 24, 2014 9:31 am

Yes it does. You may shut off category counting in your admin Section.
You could also look for RPM's VqMod, removing some counting,
not actually 'disabled' by disabling the Admin Switch.

But read this as well:
http://forum.opencart.com/viewtopic.php?f=88&t=39031

Ernie

Please don't send me OC Forum Personal Messages, just contact: jti@jacob.ch
---
OC 1.5.6.5 LIGHT Test Site: http://www.bigmax.ch/shop/
OC 1.5.6.5 V-PRO Test Site: http://www.openshop.li/shop/
My Github OC Site: https://github.com/IP-CAM
2'800+ FREE OC Extensions on the World's largest Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

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

Post by Cue4cheap » Thu Jul 24, 2014 10:42 am

To clarify my question... I have turned off in admin and disabled via the VQMod also. So the same question: Does this have any use if you (have already) turn(ed) off the product count?

Also I've read the other thread, what am I specifically looking for that relates to my question?

Mike

cue4cheap not cheap quality


Active Member

Posts

Joined
Fri Sep 20, 2013 4:45 am

Post by IP_CAM » Thu Jul 24, 2014 10:50 am

Depending on, if the VqMod used disables ALL counting everywhere, exept where it's used to enable the Page/Pagebreak -Counting. Since this depends on the amount of Products in a Category, this counting must be ON, or you see Number ONE Page only...

So, the only one who kno's is the VqMod Contributor, or, whoever has it and knows OC well enough to know about.

early morning, I hit the bad

Ernie

Please don't send me OC Forum Personal Messages, just contact: jti@jacob.ch
---
OC 1.5.6.5 LIGHT Test Site: http://www.bigmax.ch/shop/
OC 1.5.6.5 V-PRO Test Site: http://www.openshop.li/shop/
My Github OC Site: https://github.com/IP-CAM
2'800+ FREE OC Extensions on the World's largest Github OC Repository Archive Site.


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 46 guests