How can I find the SQL queries that are creating temporary tables on disk?

Question

I got a job maintaining a high traffic webapp that uses MySQL. I'm using MySQLTuner, a Perl script that makes recommendations to improve MySQL performance. Recently I've been getting the following error from MySQLTuner:

[!!] Temporary tables created on disk: 49% (50M on disk / 102M total)

How can I find out which queries are creating temporary tables on disk?

Answer 1

Search your perl code for CREATE TEMPORARY TABLE. For searching stored procs and stuff on the backend (if you're using TOAD) you can do Tools->Object Search then search for that same string. Or dump the DB and search the dump text file for that string.