Setting up Query Caching


#1

I need to speed up queries in a Lucee application by caching them but I can’t seem to find out how to set up query caching in the administrator.

There are many queries in this app that just read data from a big MySQL database, usually with from-to dates, usually “to” is = today, so:

SELECT x,y,z FROM table WHERE ID = 123456 AND date >= ‘2017-01-01’ and date <= GETDATE()
The next time someone asks for the same data, it would ideally come from the cache, as past data will never change.

Is there a global way (admin) to cache all queries for a day, for instance? because “to” (=today) will be different tomorrow. Or do I misunderstand something here? I never used query caching much in all these years.

I look at the Lucee admin page for “Cache” and I have no idea what to do. The Lucee documentation is very sparse at this point. I’d be grateful for any hints or links.


#2

It’s configured per query, you really wouldn’t wanna global setting


#3

It is part off the cfquery tag.

First thing to check is if it a common query and it doesn’t change. That would be a good candidate for a cache. In your example if that EXACT same query is executed over and over again then use a cache. If the ID or date constantly changes then a cache probably won’t help.

If the parameters of the query change often, then you need to look at the query.

Lucee debugging turned on can show the time to execute the query and then you can use your database tools, like explain, to tune the query if needed.