MySQL Slow Query Log - SELECT /*!N SQL_NO_CACHE */

Question

Using mysqldumpslow, I can see that the most common entries in a MySQL slow query log are of the following form:

SELECT /*!N SQL_NO_CACHE */ from wp_posts (or wp_comments).

I can't seem to find much information about what this statement means and whether I need to worry about it enough to try and track down where WordPress is creating this SQL.

Answer 1

These types of SELECT are always performed by mysqldumps.

If you look in the slow log for the start time of the query and the time of each of these SELECTs is the same time every day, it is definitely coming from a mysqldump you have crontab'd somewhere.

To eliminate this, you may need to run this

SET GLOBAL slow_query_log = 'OFF';

Then go run the mysqldumps, and then run

SET GLOBAL slow_query_log = 'ON';

This should totally eliminate this type of SELECT from landing needlessly in the slow log.

Answer 2

This syntax:

SELECT /*!N SQL_NO_CACHE */ * from wp_posts

is used by mysqldump. Are you using that via cron?

/* !50123 ... */
means to include the "..." only if you are running version 5.1.23 or later. This allows mysqldump (and other general tools) to use the same code, but have it work on old systems that did not have the feature mentioned.

Here's one I use for monitoring:

SHOW /*!50000 GLOBAL */ STATUS

It will execute as SHOW STATUS on old servers or SHOW GLOBAL STATUS on 5.0.0 and newer servers.