A note from 2026: This article was published in 2016. The “hybris” product name has since been replaced by SAP Commerce Cloud, and MySQL’s built-in Query Cache was removed in MySQL 8.0; the FlexibleSearch caching considerations remain relevant when application-side query caching is enabled.

Short introduction to query caching

Query caching is a common feature of the database layer that caches the result set returned by each query, which enables identical selects to execute faster because the data is fetched from memory.

In MySQL, a query cannot be cached if it contains some functions, such as CURDATE(), NOW(), LAST_INSERT_ID(), TIMESTAMP(), UNIX_TIMESTAMP() (28 functions in total).

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. The hybris Cache is designed not only for database queries, but in this article, we will cover only query caching.

Thus, there are two caching layers: on the application side and in the database. A simplified diagram of the process looks like this:

Simplified diagram of Hybris and database query caching layers

What is wrong with hybris Cache?

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

Any real-world examples?

This is important for projects where some data may become stale: schedules, timetables, time-bound auction sales, 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 issue explained above may go 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 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 the database nor by hybris. Generally speaking, I recommend using JOINs rather than subqueries because JOINs are faster. However, if your subquery is used in multiple queries, taking it out into a separate query may improve performance.