♥bruyndoncx Posted March 31, 2017 Share Posted March 31, 2017 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 More sharing options...
BrockleyJohn Posted April 7, 2017 Share Posted April 7, 2017 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 More sharing options...
mattsc Posted April 10, 2017 Author Share Posted April 10, 2017 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 More sharing options...
♥kymation Posted April 10, 2017 Share Posted April 10, 2017 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 More sharing options...
BrockleyJohn Posted April 10, 2017 Share Posted April 10, 2017 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 More sharing options...
♥bruyndoncx Posted April 10, 2017 Share Posted April 10, 2017 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 More sharing options...
BrockleyJohn Posted April 10, 2017 Share Posted April 10, 2017 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 More sharing options...
♥John W Posted April 10, 2017 Share Posted April 10, 2017 Funny you guys call it "pond". In Florida, ponds have alligators! I'm not really a dog. Link to comment Share on other sites More sharing options...
tgely Posted April 10, 2017 Share Posted April 10, 2017 Who prized here? Play fair!http://www.oscommerce.com/forums/topic/410910-also-purchased-query-performance-problem/page-2#entry1755321I dont see @@bruyndoncx mark in this tread @@mattsc. 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 More sharing options...
mattsc Posted April 15, 2017 Author Share Posted April 15, 2017 Who prized here? Play fair! http://www.oscommerce.com/forums/topic/410910-also-purchased-query-performance-problem/page-2#entry1755321 I dont see @@bruyndoncx mark in this tread @@mattsc. 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 More sharing options...
Dan Cole Posted April 15, 2017 Share Posted April 15, 2017 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 Need help? See this thread and provide the information requested. Is your version of osC up to date? You'll find the latest osC community version (CE Phoenix) here. Link to comment Share on other sites More sharing options...
BrockleyJohn Posted April 23, 2017 Share Posted April 23, 2017 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 More sharing options...
Dan Cole Posted April 23, 2017 Share Posted April 23, 2017 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: Need help? See this thread and provide the information requested. Is your version of osC up to date? You'll find the latest osC community version (CE Phoenix) here. Link to comment Share on other sites More sharing options...
♥John W Posted April 23, 2017 Share Posted April 23, 2017 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 More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.