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.

  • 1
    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.) – joanolo May 20 '17 at 18:48
  • @joanolo JDBC is used in the web application server, which is using Play and Slick. – Douglas Myers-Turnbull May 20 '17 at 19:30

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) = ...).

| improve this answer | |
  • 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 May 20 '17 at 19:33
  • 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. – Douglas Myers-Turnbull May 20 '17 at 19:34
  • Alas, another case of a 3rd party layer getting in the way? – Rick James Oct 30 '17 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.