Short introduction to query caching

Query caching is a common feature of the database layer that feature that caches the result set returned by each query which enables the identical selects to execute faster as the data fetches from the in memory. In MySQL, a query cannot be cached if it contains some of the functions, such as ,   , , ,  (in total 28 functions) . The same feature is implemented in all other database servers. However, if your application makes too many requests and most of them are cacheable, it is reasonable to use the local query cache on the application side. The hybris Cache carries out this mission. hybris Cache is designed not only for database queries, but in this article we will touch only query caching. Thus, there are two caching layers: on the application side and in the database. The simplified diagram of the process looks like:

What is wrong with hybris Cache?

Unlike the database query cache, the Hybris Query Cache doesn’t analyze the query, the functions used in it, and eventually caches everything. It means that if your query uses datetime functions such as NOW() or TIMESTAMP(), you will possibly have the same result as you get a minute ago that is incorrect.

Any real world examples?

It is important for the projects where some data may stale. Schedules, timetables, time-bound auction sale etc. The query cache will be invalidated once something or someone changes the data in the database. So if the external system updates your data every minute, the explained issue may not be unnoticed, because the maximum delay of incorrect data is less than one minute.

Demo

How to address the issue?

The simplest advice is DON’T USE THE DATETIME FUNCTIONS IN THE QUERY. If you have to, use query parameters. However, if you put the exact time in the query, the cache will never work, because every query will be unique. If you set seconds to zero, the queries within one minute will be cached.

private Date getTimestamp()
 {
 final Calendar c = Calendar.getInstance();
 final Date currentTime = getTimeService().getCurrentTime();
 c.setTime(currentTime);
 c.set(Calendar.SECOND, 0); // set seconds to zero
 return currentTime;
 }
...
 String query = "SELECT {PK} FROM {OBJECT} where {modified} > ?timestamp";
 Map queryParams = new HashMap();
 queryParams.put("timestamp", getRoundedTimestamp());
 final FlexibleSearchQuery query = new FlexibleSearchQuery(query, queryParams);
...

Subqueries

By the way, subqueries are also not cacheable neither by database nor hybris. Generally speaking, I recommend using JOINs rather than subqueries because they JOINs are faster. However, if your subquery is used in multiple queries, taking it out into the separate query may improve performance.

© Rauf Aliev, October 2016