List "copying to tmp table on disk" MySQL queries

Question

Sometimes the MySQL database we use generates files on the /tmp disk to temporary store results when performing a query.

On StackOverflow there are a lot of posts on how to disable this behavior or speed it up by for instance using a RAM disk.

I'm looking however for an elegant way to save the queries that have generated such files since I believe most of these queries can be optimized.

Is there a way to log these queries?

Answer 1

Given such queries usually take a long time, we can assume these are all slow queries. We can log slow queries using the slow_query_log_file and slow_query_log variables:

-- set the log file location
SET GLOBAL slow_query_log_file = '/path/to/slow_log_file.log';
-- turn on slow query logging
SET GLOBAL slow_query_log = ON;

Make sure that the mysql user (or the user than runs the MySQL daemon) has write access rights to the directory and file.