Is it useful to turn on MySQL query cache for performance

Question

Now the MySQL default query cache is disabled, as I remember long time ago, the hit rate of MySQL is quite high when running WordPress.

So I am thinking if enable it now will improve performance, anyone tried?

Answer 1

General speaking yes for read-only applications (until MySQL 5.7, it's deprecated since version 8.0), it enables equal SELECTs to return data extremely fast if identical calls are already stored in cache. You should consider that:

• Only exact same clauses can benefit from the cache engine (no spaces, no comments, no actual differences in WHERE expressions);
• If you are updating often the table you will not benefit much of it, since the queries get invalidated, and the invalidation algorithms can also reduce performance, in some cases.

Excellent alternatives are:

• External caching engines (i.e. Memecached or Redis, my favorite, https://redislabs.com/wp-content/uploads/2016/03/15-Reasons-Caching-is-best-with-Redis-RedisLabs-1.pdf)
• ProxySQL (since MySQL 8.0) on which I have no experience. Read more https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/

I think also if you combine those with a good web server like NGINX or Apache2 and an HTTP accelerator like Varnish you can fly.

As I know, these are also the best current practices but I might have ignored something, please share it in case.

Answer 2

MySQL's query cache was never much good and has been retired since version 8.0 So, if you're looking for optimizing database performance with WordPress, that's not the way to go. WP does have a tendency to bloat the database, however. There are several things you can do to prevent this.

• Limit revisions of your posts to 2 by including define( ‘WP_POST_REVISIONS’, 2 ); in wp-config.php. Or stop revisions alltogether with define( ‘WP_POST_REVISIONS’, false ); In phpMyAdmin you can delete existing revisions with DELETE FROM wp_posts WHERE post_type = "revision";.
• Database cleanup. When you disable a plugin, its data will usually be left in the database, so when you enable it again, you can pick it up where you left it. This can amount to a lot of data, though. All sorts of other orphan data may slow down MySQL as well. There are plenty of plugins that will help you clean up this stuff.
• Use the Query Monitor plugin to identify slow queries. A lousy script in your theme might be the culprit of a slow database and caching won't help with that.