SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2002
    Location
    UK
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Query cache / synchronising two tables

    I have a downloads table consisting of 230,000 records. This table is constantly searched in up to two varchar(255) columns, which appears to be causing a great deal of problems during peak times. From looking at my slow query log, this seems to have an adverse affect on a range of queries that fetch and sort rows of common download data.

    I've been looking at some solutions to the problem and I've come to the assumption that I should cache common search queries and try to utilize the MySQL query cache so that those more common queries are read from memory. However, my download table is constantly updated with download counts and ratings so switching on the query cache is pretty much useless as it currently stands.

    Would it be possible/sensible to replicate the downloads table within the same database and then synchronise the two tables every few hours? Could anyone explain how I could do this? Is the query cache even suitable for rows of commonly fetched data?

    Also, with regard to a search cache, would anyone know where I can find more info/advice on how to build a search system that uses a cache system for common keywords, or give me a basic idea of how a system like this might work?

    Any help would be appreciated.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    split the table in to two parts: one with the varchar columns you want to search, and the other with all of the other columns that are frequently updated.

    make sure that queries that touch the varchar table do not join any other tables. otherwise when those joined tables are updated, the cached entries for the varchar table will be wiped. in many cases, this probably means that what you're currently accomplishing in 1 query will now take two.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


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
  •