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?
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.