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.