Monday, August 02, 2010

Website Performance: Cache Database Query Results

Querying the database is an expensive operation and should be kept to a minimal.

Certain databases provide query caching capabilities. MySQL's query cache is great for tables which are used primarily for read operations Any insert/update query clears the complete query cache for the table. Thus, query caching cannot be leveraged for tables requiring regular insert/update operations.

Adding caching capabilities above the database layer can help boost performance. Before passing a read request to the database, an additional layer can check for appropriate content in the cache. If content is not available in the cache, request can be forwarded to the database and the cache populated before returning the result to application.

The caching layer can also trap any insert/update operation so that the cache is up-to-date.

If you are using Hibernate to persist your objects, the second level cache (and query cache) should be considered. They help achieve the same performance benefits using application level caching.

No comments: