Can queries containing deterministic user defined functions be cached in mysql query cache?

Posted on

Question :

According to the mysql 5.6 docs:

A query also is not cached under these conditions:

It refers to user-defined functions (UDFs) or stored functions.

However, I have seen claims that if the UDF is completely deterministic, the query can in fact be cached. Can someone confirm or deny that? If indeed UDFs do prevent caching, is there any smarter way to allow the queries to be cached than to simply inline the UDF into each query? (That would work, but would obviously be cumbersome.)

Edit: Also, is the behavior different for stored functions vs user-defined functions written in C?

Answer :

Based on some experimentation with MySQL 5.5.33, the answer is “no”. I have a function definition which is deterministic (it’s a simple date calculation) and is marked as DETERMINISTIC. If I use it in a query, the query is not cached. If I replace the call by its definition in terms of the built-in date_sub() function, the query is cached.

Leave a Reply

Your email address will not be published. Required fields are marked *