Using cache for database query

I recently think about how to use cache to improve database query performance. However, one may know one way is to cache the query result for those tables that have relative fewer update. I consider this and think of the whole algorithm as below pseudocode (I am using Java styles).

(1) Simple Query Cache

//Firstly, get the sql string

String sql = this.getSqlStatement(); Object value = this.getCache(sql);

if (value == null) {

    //execute the sql statement to retrieve data from database

    Object result = this.execute(sql);

    //set the result in cache, using sql as the key

    this.setCache(sql, result);

    return result;

} else {

    return value;

}

Above algorithm puts no consideration about querying stale data. Other operations, such as update, on the database may cause the query to have incorrect results. Therefore, I am thinking to keep a list of modified tables, using that for determining which table is updated after query result is cached. The following is my new algorithm.

(2) Sophisticated Method

For each update, delete, insert operation, 

mark the timestamp of execution and the table that is modified,

put the data in cache.

For each query

       Get the table modified timestamp T1 from cache

       Get the cache timestamp T2 for query

       if (T2 < T1) {

           execute the query

           set the query into the cache

           set the timestamp into the cache

           return the query result

       } else {

            get the cache

            return the cache

      }

Now, the key is that we need benchmark tests to ensure the performance increase. I will write that later.