Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
Yup, the timings match - thanks. –  anu Jul 13 '11 at 16:21

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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