Category: Community

We are looking for community feedback on the use-cases for the Query Cache in MySQL.

As astute followers will notice, the query cache is now off by default in 5.6, but realistically it’s always been disabled because the previous configuration was ON with zero memory allocated to it.

The reason for its disabling, is that the query cache does not scale with high-throughput workloads on multi-core machines. This is due to an internal global-lock, which can often be seen as a hotspot in performance_schema.

The ideal scenario for the query cache tends to be largely read-only, where there are a number of very expensive queries which examine millions of rows only to return a few. A hypothetical example might be a complex query to build a list of values for a drop-down list that always appears on a webpage form. In a situation like this, the query cache can mask performance problems caused by missing indexes, which makes it helpful for novice users.

My opinion however (and it does not necessarily reflect that of the MySQL team), is that this scenario is becoming less frequent:

• Many users are building queries via ORMs, which can often offer caching directly.
• Tooling is getting better, and novice users can discover missing indexes via things like MySQL Workbench’s visual explain.
• Any updates can invalidate the cache, and good performance practice teaches us that we should not pay attention to the average, but try and tighten the variance between user requests.

Have I missed anything? Please leave a comment, or get in touch. I would love to hear how you are using the Query Cache.

In the MySQL team, we’ve been discussing removing the non-standard \N synonym for NULL. For Example:

mysql> SELECT NULL is \N;
+------------+
| NULL is \N |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql> INSERT INTO tablea VALUES (3, \N);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tablea WHERE b IS \N;
+---+------+
| a | b    |
+---+------+
| 3 | NULL |
+---+------+
1 row in set (0.00 sec)

The use of “\N” in files loaded with LOAD DATA INFILE or created with SELECT INTO OUTFILE will be unaffected by this change and continue to be supported.

The two options we’ve laid out are:

Option #1:
– Add deprecation warnings in MySQL 5.6 and MySQL 5.7
– Remove in MySQL 5.8

Option #2:
– Add deprecation warnings in MySQL 5.6.
– Remove in MySQL 5.7

Do you rely on this behavior in your application?
Will option #2 affect you negatively?

Please feel free to leave a comment, or contact me.

Thanks!