Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

PHP 5 and MySql 5 Upgrades Slowed Search to a Crawl


PropioWeb

Recommended Posts

We recently had our server upgraded to:

 

MySQL version 5.0.91-community

PHP version 5.2.14

 

And everything seemed to go smoothly with no changes to the code at all but then I tried a search and wow is it slow. At first I thought the page was broken it took so long to load.

 

Click here for example: http://www.jehovanisi.com/advanced_search_result.php?keywords=Lourdes+Toledo&x=33&y=6

 

I don't understand why the rest of the site is running smoothly, faster even but advanced_search_results.php is so gosh darn slow.

 

We are running a heavily modified osC 2.2 MS2, so upgrading will be quite the task. Does anyone have nay idea what could be causing the search to be so slow? I've done some searching on the forums with no real answers.

 

Thanks in advance.

Samuel Mateo, Jr.

osC 2.2 MS2

Installed Mods:

WYSIWYG HTMLArea 1.7 | Basic Template System 1.0 | osC-Affiliate | OSC-SupportTicket

Featured Products 1.3 | LoginBox 5.2 | LatestNews 1.1.3 | Extras for IE

Link to comment
Share on other sites

BTW I tried the patches suggested here: http://www.oscommerce.com/ext/update-20060817.txt but that did not fix it and actually some of the patches were already done.

 

Any help would be much appreciated.

Samuel Mateo, Jr.

osC 2.2 MS2

Installed Mods:

WYSIWYG HTMLArea 1.7 | Basic Template System 1.0 | osC-Affiliate | OSC-SupportTicket

Featured Products 1.3 | LoginBox 5.2 | LatestNews 1.1.3 | Extras for IE

Link to comment
Share on other sites

Also wanted to mention that we are not getting any 1054 errors so not sure if making those changes will help with this issue. Or will they?

Samuel Mateo, Jr.

osC 2.2 MS2

Installed Mods:

WYSIWYG HTMLArea 1.7 | Basic Template System 1.0 | osC-Affiliate | OSC-SupportTicket

Featured Products 1.3 | LoginBox 5.2 | LatestNews 1.1.3 | Extras for IE

Link to comment
Share on other sites

I don't understand why the rest of the site is running smoothly, faster even but advanced_search_results.php is so gosh darn slow.

It might have been always slow because it is. It joins a pretty big number of tables in the query. So with a large number of products (so big tables) things start to get slow.

 

I don't know of any contributions that handle this (although I have an idea on how to try to tackle this, but fail a large site to try it on). In the upgrades an index to the table specials was added that might help here:

 

alter table specials add index idx_specials_products_id (products_id);

 

An idea might be to install a contribution to see how many queries a page generates and how long it takes to execute them (find excessive amount of queries or slow ones) : KissER Error Handling & Debugging (needs PHP5.2 or higher so no problem for you here).

Link to comment
Share on other sites

 

alter table specials add index idx_specials_products_id (products_id);

 

What does that do to the tables?

Samuel Mateo, Jr.

osC 2.2 MS2

Installed Mods:

WYSIWYG HTMLArea 1.7 | Basic Template System 1.0 | osC-Affiliate | OSC-SupportTicket

Featured Products 1.3 | LoginBox 5.2 | LatestNews 1.1.3 | Extras for IE

Link to comment
Share on other sites

Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile

 

Virus Threat Scanner

My Contributions

Basic install answers.

Click here for Contributions / Add Ons.

UK your site.

Site Move.

Basic design info.

 

For links mentioned in old answers that are no longer here follow this link Useful Threads.

 

If this post was useful, click the Like This button over there ======>>>>>.

Link to comment
Share on other sites

@Jan Zonjee You are the man! That fixed it up right quick. Wow. Thanks so much. The search is now running as fast as the rest of the site.

 

@geoffreywalton thanks for clearing that up.

Samuel Mateo, Jr.

osC 2.2 MS2

Installed Mods:

WYSIWYG HTMLArea 1.7 | Basic Template System 1.0 | osC-Affiliate | OSC-SupportTicket

Featured Products 1.3 | LoginBox 5.2 | LatestNews 1.1.3 | Extras for IE

Link to comment
Share on other sites

  • 7 months later...

It might have been always slow because it is. It joins a pretty big number of tables in the query. So with a large number of products (so big tables) things start to get slow.

 

I don't know of any contributions that handle this (although I have an idea on how to try to tackle this, but fail a large site to try it on). In the upgrades an index to the table specials was added that might help here:

 

alter table specials add index idx_specials_products_id (products_id);

 

An idea might be to install a contribution to see how many queries a page generates and how long it takes to execute them (find excessive amount of queries or slow ones) : KissER Error Handling & Debugging (needs PHP5.2 or higher so no problem for you here).

You're a genius!!!

A client's store was taking an average of 30s to load subcategory pages. The specials table had almost 4000 products and it was slowing down the whole store. Dropped the specials table, the store run smooth. But of course this was not the answer, for every store needs to have specials.

 

This little command did the trick and now the store is running fast like a Ferrari. :)

Tks a lot!!! Just want to bring this back so it might help other people.

Patty

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...