Even when the output of EXPLAIN doesn’t show “using temporary”, a temporary file may still be used in certain cases.
That’s not to say the query needs the temporary file to actually resolve the query (like what you’d see from the need for a derived table). But rather, the temporary file I’m speaking of is due to binary logging.
In particular, you can see this easily if using InnoDB, (most
commonly) row-based binary logging, and you issue a
large transaction, say a large UPDATE (large meaning
something larger than the size of binlog_cache_size). In this case, you’ll
notice a temporary file being …