Caching item IDs in a memory table - good/bad?

Question

I was thinking....I have a site where there is a lot of searching/sorting going on. What if I cached the result item IDs for each search into a MEMORY table, and then simply did WHERE item_id IN ("1", "5", "44", "67").

If there is no value for the particular query, it does the full query, and writes the IDs into the MEMORY table.

Will this offer a significant performance boost? or should I just cache the HTML of each search result into an html file and display that?

Answer 1

The potential performance boost depends on how often the same query is reused.

There will be an extra step to scan the MEMORY table to see if that query has been run recently. For unique queries with small datasets, this might actually decrease your performance and unnecessarily add complexity to your system.

As for putting your HTML into an output cache of some sort, I would recommend you do this regardless of any query caching you might do if your traffic is high enough to justify doing this.

Above all, only optimize when necessary. Adding complexity to a system unnecessarily doesn't add value and can make maintenance harder than it should be.

Answer 2

As has been said elsewhere, if your query is reused then you could have a performance boost. But your query must return the same results as well. If your dataset is not static, then you will have to redo the query anyway.

There are a lot of options to improve performance. If you have exhausted all of the options do with indexes, increasing hardware on the server, increasing the bandwidth of the network between your server and client(s), then you could explore some other options:

• cache the HTML for a request: note that this will cache the results for a search & the sort used; if the user changes the order of one of the columns, then you'll need to redo the query (*)
• cache the id's of the search, but not the order by. More complex to handle, but has the benefit that if the user changes the column order, the query is still as fast. You can even pre-cache some common queries

As always, the performance of your system depends upon a lot of factors. To really answer your question you need to measure the performance on your system. If you have a doubt, measure. I've found a few times that the performance gain is negligable.

You also have to take into account the complexity you're adding to the system by performance optmizations like this, and the flushing of the cache, what happens if data changes, how do you handle all of these problems.