Disallowing MariaDB query cache except from webserver (JDBC)

Question

I have an application server (Play Framework), webserver (Caddy), and database server (MariaDB with InnoDB) that are running on a single machine for now.

The database is used in two ways:

• client Python code that makes highly specific SELECTs (over an SSH tunnel).

• the web/application server, which performs occasional INSERTs and UPDATEs, but mostly SELECTs from large tables each time a page loads.

Is there a simple way to enable (and force) query caching for the webserver but not for the client code? My thought was to set query_cache_type=DEMAND and use SQL_CACHE on the webserver, but it's not ideal. Perhaps I can force JDBC to use SQL_CACHE somehow?

Clarification: The unfortunate technical issue with adding SQL_CACHE to every query is that the web application queries using Slick and a JDBC backend. The integration with Slick is tight so I can't rewrite the application layer, and I don't know how to force JDBC to append SQL_CACHE.

• joanolo: Which role does JDBC play in this scenario? Is it used by the Python client? (My guess is it isn't, and you're connection is using mysql.connector and JDBC isn't playing a role.)
• Douglas Myers-Turnbull: @joanolo JDBC is used in the web application server, which is using Play and Slick.

Answer 1

If there is a lot of write activity, the Query Cache is virtually useless -- because all QC entries for a table are purged when any modification happens to the table.

If the QC is useful, using query_cache_type=DEMAND and manually saying SQL_CACHE or SQL_NO_CACHE on every SELECT may be optimal. This keeps the rare, bulky, queries from cluttering the cache, while allowing the 'good' ones to make use of the QC. Note: This has nothing to do with the connector (JDBC, etc), only with the SELECT statements.

Yes, DEMAND without SQL_CACHE leads to not using the QC. I agree that this is "not ideal" since it depends on un-obvious rules about the QC. Still, it may be your best solution, other than...

If a query is slow slow that it matters, we should look critically at the query. The speed-up may be as simple as adding a 'composite' index, or some seemingly minor tweak to the query such as not hiding an indexed column in a function call (eg, DATE(dt) = ...).

• Douglas Myers-Turnbull: Thanks for the reply! There are very few writes. The unfortunate technical issue with adding SQL_CACHE to every query is that the web application queries using Slick rather than raw SQL.
• Douglas Myers-Turnbull: I don't know how to force Slick or JDBC, which it uses, to append SQL_CACHE, and rewriting most of the application layer to use raw SQL isn't an option at this point.
• Rick James: Alas, another case of a 3rd party layer getting in the way?