What does "SELECT /*!N SQL_NO_CACHE */ * FROM `mytable`" mean in in MySQL's slow query log?

Question

I've just turned on slow query logging on my MySQL database, adding the following to /etc/mysql/my.cnf:

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1

When I run mysqldumpslow, it outputs the following:

Reading mysql slow query log from mysql-slow.log
Count: 1  Time=199.23s (199s)  Lock=0.00s (0s)  Rows=32513.0 (32513), ...
 SELECT /*!N SQL_NO_CACHE */ * FROM `mytable`

...

Looking at the original mysql-slow.log, the full query was:

SELECT /*!40001 SQL_NO_CACHE */ * FROM `mytable`;

So mysqldumpslow just replaced the number with N (to assist aggregating similar queries.)

So, the question is, where does that query come from and what does the /*!40001 SQL_NO_CACHE */ bit mean?

As best I can tell, it's probably from a mysqldump command that was doing a backup (hence not wanting cached data), does that seem right? And if so, since it only read 32,000 rows, why did it take 199s?

There are a bunch more similar queries on other tables taking 100s, 50s, down to a more reasonable 3s, most having about 10-20,000 rows, the biggest with 450,000 rows.

Answer 1

The /*!40001 SQL_NO_CACHE */ means that in versions of mysql >= 4.0.1 execute SELECT SQL_NO_CACHE * FROM mytable and in earlier versions execute the command without the SQL_NO_CACHE.

Also mysqldump does use the /*!40001 SQL_NO_CACHE */ syntax.

I'm not sure why your queries would be so slow.