Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Also Purchased query performance problem


mattsc

Recommended Posts

just to be certain, also optimze products_description

 

this is an alternative query, maybe it is faster, maybe not, on mine there is no difference

select distinct p.products_id, p.products_image, pd.products_name
  from products p join products_description pd using (products_id)
  join orders_products op using(products_id) 
 where orders_id in (select orders_id from orders_products where products_id = 3048 )
   and products_id != 3048
   and p.products_status = '1'
   and pd.language_id = 1
   order by orders_products_id desc
   limit 3
you could force to limit the order range by adding a limit to the orders to consider

and orders_id > xxxx
possibly additional indexes on orders_products can help, so that all necessary data is found in indexes, and no additional table reads are needed

create INDEX idx3_orders_products on orders_products(products_id, orders_id)
create INDEX idx4_orders_products on orders_products(orders_id,products_id)
create INDEX idx5_orders_products on orders_products(orders_id,products_id, orders_products_id)
still, on my system, everything returns in 0.015secs interactively, makes no difference

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

Here's something else to have a go at: try making a copy of your database and run the query against that for comparison. I worked on a system once with a five-year-old database where the performance suddenly fell off really badly. None of the optimisation or repair options available within phpMyAdmin improved it but replacing it with a copy worked.

 

At the time I put it down to an overloaded server (on shared hosting - the copy was on a different one) but I have no evidence that was actually the cause.

Contact me for work on updating existing stores - whether to Phoenix or the new osC when it's released.

Looking for a payment or shipping module? Maybe I've already done it.

Working on generalising bespoke solutions for Quickbooks integration, Easify integration and pay4later (DEKO) integration at 2.3.x

Link to comment
Share on other sites

Talked with a DBA buddy of mine, and offered him a prize to fix this... he came back in about 20 minutes with a fix that took my query time down from ~49 seconds, down to 0.14 seconds. This fix is 3 additional indices:

create index products_id_orders_id on orders_products (products_id, orders_id);
create index products_id on orders_products (products_id);
create index orders_id   on orders_products (orders_id);

The second and third indices are optional. The first one was THE key. That took the query down to 1.03 seconds. The next two took me down to 0.16 seconds and 0.14 seconds. 

Link to comment
Share on other sites

Stock osCommerce already has the last two indices. It would be interesting to test the difference with those and not the first one.

 

Regards

Jim

See my profile for a list of my addons and ways to get support.

Link to comment
Share on other sites

Talked with a DBA buddy of mine, and offered him a prize to fix this... he came back in about 20 minutes with a fix that took my query time down from ~49 seconds, down to 0.14 seconds. This fix is 3 additional indices:

create index products_id_orders_id on orders_products (products_id, orders_id);
create index products_id on orders_products (products_id);
create index orders_id   on orders_products (orders_id);

The second and third indices are optional. The first one was THE key. That took the query down to 1.03 seconds. The next two took me down to 0.16 seconds and 0.14 seconds. 

 

So - Jim's second tip was the hot one: http://www.oscommerce.com/forums/topic/410910-also-purchased-query-performance-problem/?p=1755150

Contact me for work on updating existing stores - whether to Phoenix or the new osC when it's released.

Looking for a payment or shipping module? Maybe I've already done it.

Working on generalising bespoke solutions for Quickbooks integration, Easify integration and pay4later (DEKO) integration at 2.3.x

Link to comment
Share on other sites

hhhmmmm, raising hand here :D

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

ok there was also a hot one this side of the pond :D

Contact me for work on updating existing stores - whether to Phoenix or the new osC when it's released.

Looking for a payment or shipping module? Maybe I've already done it.

Working on generalising bespoke solutions for Quickbooks integration, Easify integration and pay4later (DEKO) integration at 2.3.x

Link to comment
Share on other sites

:blink:
osCommerce based shop owner with minimal design and focused on background works. When the less is more.
Email managment with tracking pixel, package managment for shipping, stock management, warehouse managment with bar code reader, parcel shops management on 3000 pickup points without local store.

Link to comment
Share on other sites

 

Sorry, I hadn't seen the reply when I posted the update. I just opened the thread and posted my message and marked it as fixed. I have since gone through and read the reply from @@bruyndoncx  and see that yes there was a similar finding of the additional index. I wasn't trying to steal anyones thunder but was just posting the result that solved the problem I'd posted about. Sorry. I wasn't trying to cause anything by hitting mark solved other than to set it as resolved.

Link to comment
Share on other sites

Funny you guys call it "pond".  In Florida, ponds have alligators!

 

If you live in the UK, alligators are from across the pond. :lol:

 

Dan

Link to comment
Share on other sites

If you live in the UK, alligators are from across the pond. :lol:

 

Dan

 

and gaiters stop the rain and snow getting in the top of your boots

Contact me for work on updating existing stores - whether to Phoenix or the new osC when it's released.

Looking for a payment or shipping module? Maybe I've already done it.

Working on generalising bespoke solutions for Quickbooks integration, Easify integration and pay4later (DEKO) integration at 2.3.x

Link to comment
Share on other sites

and gaiters stop the rain and snow getting in the top of your boots

 

When you have allagators, while helpful, you don't worry about boots. :rolleyes: 

Link to comment
Share on other sites

There are some spots with brackish water on one side and fresh on the other.  You can see saltwalter crocs and alligators from one spot.  So, gators and crocs! 

I'm not really a dog.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...