Pros and cons with auto / manual cache
I think a lot about whether to start a full cache or a manual cache.
Our automated approach would be a solution that breaks through the database, queries and formats every potential and future data request, and saves it to the appropriate cache store (memcache or disk based). The data will then not be invalidated and just overwritten by the automatic cache refresh action.
The manual approach is to have each request check if there is a valid and stored version of the cache that contains the requested data, and if not recreate it and store it in the appropriate cache store.
What's common sense? How do the big guys do?
Thank you very much!
a source to share
The answer is of course: it depends! If you are getting a lot of requests for a sparse subset of your data, then on-the-fly caching may be better than caching everything. For example, if you were querying a database of hotel rates with a complex query and only a few hotels were hitting hits frequently, it would be impractical to cache every search for every hotel. Wait for the user to request something and then cache that result.
If, on the other hand, you have a widely available set of databases, you can look into something like a materialized view http://en.wikipedia.org/wiki/Materialized_view.This basically makes the database for all the caching for you and materializes a query for a complete set into a "cached" table.
a source to share