MySQL Forums
Forum List  »  General

Is there a known pattern for creating caches of data?
Posted by: David Hicks
Date: May 31, 2013 11:38AM

We have several "cache" tables that get re-computed periodically (every 5 to 15 minutes), in order to enhance the performance of look-ups against the data. Right now, our methodology is to do something like this...

1) create a temporary (not traditional TEMPORARY memory table) table in the same format as the cache table.
2) recompute the cache, placing the data into the temporary table
3) Do any indexing, updating, etc. necessary to the temp table.
4) DROP the original table.
5) RENAME the temp to be the new original.

Between steps 4 and 5, we often get requests for data, resulting in errors with "table does not exist."
I'm wondering if there is a better, more efficient way of doing something like this.
The amount of data involved is far too large to employ REPLACE-INTO - it creates too many locking problems.
Thanks!
Dave

Options: ReplyQuote


Subject
Written By
Posted
Is there a known pattern for creating caches of data?
May 31, 2013 11:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.