A site I’ve been working on has started to get very slow with increased traffic.
Search result combine listing combined from several tables. What is the best strategy for speeding this up?
I was thinking caching an XML file might work. Though I’m concerned writing the entire table as xml may run into memory limits. Another idea is to compile the complete listings in a table so they wouldn’t need to get assembled on the fly.
Any thoughts on this?
E
What kind of searches are performed on the data?
Writing to XML probably won’t make it faster, because XML is not a very fast nor efficient format to use. You’re probably better off using some caching mechanism like memcache, XCache or APC.
Alternatively, depending on the data, you may want to look at alternative servers to MySQL, like Solr, which is a lot better and faster at searching large quantities of data than MySQL will ever be.
An example search would be to filter blog entries by theme and location
Each entry would have information drawn from the blog table, member table, location table, etc… for the display.
I researched you recommendations but couldn’t figure out where to start and how to install them.
So I went with my original plan and set up a cron job to update a cached “profile” field to the members’ table for searches.
This cut the delivery of the html from 10 seconds to 600ms.
This may not be optimal, but it worked.
This is not the fast option, but might lead to insights.
Log what the actual search terms are (and if possible the number results found) and look for patterns (frequent terms, terms by time of day, terms by type of user and so on).
After some time you will spot patterns emerging which may lead you to:
a) pre-cache some results
b) alter your gui and navigation to make these top tasks more accessible early on to your users and make your site easier to use.
c) compelling evidence of “missing content” - stuff your users search for - but never find
d) mismatch of terms, you call it a “swimming pool ingester” but your users search for “pool cleaner”
The solution you found sounds good, but doing the above might lead you to other solutions, and to ask fundamental questions such as “why are our users searching?”
Thank you Cups. These searches are mostly drop down menus sorts. They can also be searched by name and country. The strategy I used created acceptable results. The downside to it is that a separate caching script needs to be created for each type of content (of which they are many). On the host the site is on now, APC isn’t an option and I don’t think the other ones ScallioXTX mentioned are either. Though they are probably more elegant solutions.
So in effect, are you saying its not “string searches” a la Google, it is filtered results? From your database?
Make sure you have indexed all your searchable/sortable columns properly.
Turn on the slow queries log temporarily and try and knock off the slowest ones, do what JV- suggests. See if you can increase the cache size on your database (sorry, I don’t know if this is really possible).