Optimizing mySQL for Zen Cart in searches

[enigmabomb]

Hi,

I have a site with almost 100k items. The speed when browsing is absolutely phenomenal. Sub .5 second load times are standard. However, when searching for any given item, I'm seeing obscenely large query times. For example:

Parse Time: 7.324 - Number of Queries: 567 - Query Time: 7.10531123407

As you can see the parse time is very very fast once it gets the results from the database.

I have a dedicated server. I can do anything to it I want. How should I tweak mysql so it searchs faster (The large majority of our customers search instead of browse)

The site is www.clubhobbies.com

Thanks for any tips you might have,

Josh

[kendur]

I had the same problem (+70,000 products)
What I did was change the code so it did not do an initial search of the descriptions.
I have some rather large descriptions and it would take a very long time to return the results. Also the returns where not very accurate at first glance.
Searching "grand theft auto" would return anything with a combination of the words.

In files
/includes/templates/template_default/sideboxes/tpl_search.php
and
/includes/templates/template_default/sideboxes/tpl_search_header.php

I changed the 1 to a 0 in the line where it says:
$content .= zen_draw_hidden_field('search_in_description', '1') . zen_hide_session_id();

to:

$content .= zen_draw_hidden_field('search_in_description', '0') . zen_hide_session_id();

This stopped the header and sidebox searches from searching the descriptions.

Ken