Query cache / synchronising two tables

[mpw]

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.

[longneck]

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.