7 replies to this topic

[PropioWeb]

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.

Edited by PropioWeb, 08 October 2010, 18:41.

[PropioWeb]

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.

[PropioWeb]

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?

[Jan Zonjee]

PropioWeb, on 08 October 2010, 18:41, said:

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).

[PropioWeb]

Jan Zonjee, on 09 October 2010, 10:54, said:

alter table specials add index idx_specials_products_id (products_id);

What does that do to the tables?

[geoffreywalton]

Try this link

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

[PropioWeb]

@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.

Edited by PropioWeb, 12 October 2010, 16:41.

[Patty]

Jan Zonjee, on 09 October 2010, 10:54, said:

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.