Speeding up SEARCH Query - Help!

[Jeff_Mash]

Ok, hopefully one of you could help me pinpoint what the problem may be.

Basically, my site is fine. It's running good, and speed is fast. However, the only SLOWNESS I get is when you use the SEARCH feature.

For example, let me give you some numbers to paint a better picture. When my homepage loads, here are my parse times:

Parse Time: 1.374 - Number of Queries: 654 - Query Time: 1.08609739244

If I go to a specific product, those are also relatively fast:

Parse Time: 0.685 - Number of Queries: 437 - Query Time: 0.265415187042

THE PROBLEM:

If I use the SEARCH feature on my site and just search for something, the response is drastically slow:

Parse Time: 8.737 - Number of Queries: 403 - Query Time: 8.4556786736

What can I do to speed up just this search results query time?

[DrByte]

Depends on a lot of information not shared here ... what's being searched (what criteria entered), where is the search being initiated from (sidebox, header, adv search page, custom page, and so on), what addons have you installed and why, how are ALL your products configured, do they have attributes, sales, specials, qty discounts, custom product types, what changes have you made to any core ZC files, have you changed any defaults in templates such as input fields connected with the search form being used, is this specific to a certain time of the day, and the list goes on.

[DigitalShadow]

search for letter 's'

Displaying 1 to 25 (of 12479 products)
Parse Time: 0.919 - Number of Queries: 1000 - Query Time: 0.743014149429


search for '6871l'
Displaying 1 to 25 (of 148 products)
Parse Time: 0.803 - Number of Queries: 1018 - Query Time: 0.629929668442

View product page
Parse Time: 0.216 - Number of Queries: 283 - Query Time: 0.0596547307739

Now compare my load times to yours

My Home Page
Parse Time: 0.299 - Number of Queries: 626 - Query Time: 0.155655704987

Your Home Page
Parse Time: 1.374 - Number of Queries: 654 - Query Time: 1.08609739244

Similar number of queries, I would say your slow search is caused by server performance and not code.

There is nothing slow about zen cart search in my opinion.

Are you on a shared server?

[Jeff_Mash]

I am on a dedicated server. I just wanted to rule out anything related to Zen Cart, since the ONLY part slow is the SEARCH feature. Everything else (like loading the front page, loading a a product page, etc) is really fast.

Here's an example which makes me wonder if it's something in my code.

On my site, if I just type in the letter 'S' in the search box, these are my parse times:

Parse Time: 2.651 - Number of Queries: 934 - Query Time: 2.34763558519

If I increase the letters, so now I search for the word "Suddenly", look at how it jumps:

Parse Time: 7.595 - Number of Queries: 936 - Query Time: 7.28549754742

The number of queries are roughly the same, but the parse & query times are almost three times slower.

I know in the past, there was a code change to return our search results based on relevance, but this didn't make the results slow. So I am just trying to narrow things down to see what it could be.

Any other ideas? Is there a way to have ZenCart search TITLE ONLY and not in the descriptions? If so, I can see if that will help. Is there something in MySQL that I should do (reindexing something, etc)? I have made sure all tables are optimized by repairing any overhead that certain tables were showing.

[Jeff_Mash]

One more thing. Do you think this had anything to do with it? Our database for zencart was originally using "latin1_swedish_ci" for the collation. We changed it all to "utf8_unicode_ci". Would that slow things down you think?

[DrByte]

which makes me wonder if it's something in my code.

Could be.

Is there a way to have ZenCart search TITLE ONLY and not in the descriptions?

Yes, there's a checkbox for that in the Advanced Search page.

Do you think this had anything to do with it? Our database for zencart was originally using "latin1_swedish_ci" for the collation. We changed it all to "utf8_unicode_ci". Would that slow things down you think?

Simple answer: Maybe. Maybe not.
Way too little information here to be able to tell, such as "did your 'slowness' problem pre-exist before you changed the collation? If yes then the change was likely unrelated. If no, then maybe it's related. And then there's the CRUCIAL all-important question about whether you actually changed the collation method safely or thoroughly. There are many things involved in that, and that's not a matter specific to Zen Cart. Also, maybe in your change of collation perhaps you dropped indexes on your tables. You should compare schemas from before-and-after and put back relevant missing indexes.

[Jeff_Mash]

I'm just trying to figure out why ONLY THE SEARCH is slow on our site. All other pages load fine. Even if you click a products link to display the Product Info page, that's fast......but the search is slow when using the search engine.

What I tried to do is compare the Zen_Products and Zen_Products_Description tables with some old backups I have. Aside from the Collation being different, the indexes are all the same.

- Are there any other tables I should compare for missing indexes which are used by the Search function?

- Which core files are used to manage the search function so that I can make sure no changes were made to them? Is there a php file under the modules directory? Under the functions directory? Perhaps if you tell me which files are used for the search feature, I can at least revert those back to the original install files to rule out any inadvertant code changes on our side.

If I revert the files back which relate to the search results and it's still slow, that would point me to a database issue. I would just think that if it was purely DB related, then the whole site would act slow and not just a specific aspect of it.

[DrByte]

I don't have a list of files off the top of my head. But they're basically the same files as the product-listing uses, since the search is just a filter applied to the product listing.

I know you've said that your site is generally fast. I still offer the following in case one or more of the suggestions in these articles (all optional) helps alleviate this or any other issues you may be having:
http://www.zen-cart.com/content.php?...eed-up-my-site (I'm thinking of parts 4 and 12 for your case)
http://www.zen-cart.com/content.php?...er-tuning-tips

[Jeff_Mash]

I believe I may have found the problem. Apparently, the issue was with changing the collation of the products_description table.

Originally, the table is set for latin_swedish_ci, but we were noticing a lot of encoding errors on our google feed. It was suggested that we change our table and any VARCHAR fieldname to a collation of utf8_unicode_ci

So what we did originally was go to MySql-->Product_Descriptions--> Operations and change the table collation. Then, we went to the structure of the table and manually changed these three fields to have the same unicode collation:

- products_name
- products_description
- products_url

That seemed to submit all of our products to google with no encoding errors. However, as you saw, it incredibly slowed down our search query on our website.

So I narrowed it down to this table and those fields, and by changing it back to latin_swedish_ci, it's now 300% faster!

Just thought I would publish this solution since it may help someone else who may happen to change the collation of the table to try and mess with the encoding for product feeds.

[DrByte]

So I narrowed it down to this table and those fields, and by changing it back to latin_swedish_ci, it's now 300% faster!

Just thought I would publish this solution since it may help someone else who may happen to change the collation of the table

I'm not surprised.

I recommend NOT messing with collation changes unless you have a SOLID understanding of the complete big picture and know what you're doing.