SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    934
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Caching searchable content

    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

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,892
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    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.
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    934
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    934
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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?"

  6. #6
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    934
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    So in effect, are you saying its not "string searches" a la Google, it is filtered results? From your database?

  8. #8
    SitePoint Enthusiast
    Join Date
    Oct 2011
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Make sure you have indexed all your searchable/sortable columns properly.

  9. #9
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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).


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •