Today I saw a query like this:
SELECT d FROM t;
Performance was terrible. I ran out of patience after several minutes and killed the thread.
I changed the query to this:
(SELECT d FROM t) UNION ALL (SELECT NULL LIMIT 0);
It completed in under 3 seconds.
Can you explain how a no-op UNION so dramatically improved performance? (I couldn’t have, without help from Jesper Krogh and James Day).
Hint #1 ▼
http://dev.mysql.com/doc/refman/5.5/en/cursor-restrictions.html
Hint #2 ▼
The field `d` is a varchar and is bigger than it needs to be.
Hint #3 ▼
http://dev.mysql.com/doc/refman/5.5/en/memory-storage-engine.html
Hint #4 ▼
http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html
Answer ▼
The query is in a cursor. MySQL materializes cursors to a temporary table, using the MEMORY engine if it can, then converting to MyISAM if it gets too big. The MEMORY engine doesn’t support variable length rows, so VARCHARs are converted to CHARs. The field `d` only contains one character in each row, but is defined as a VARCHAR(20000) – which means the temporary table will grow very large. Even when converted to MyISAM, it retains that fixed width format, so the temp table is huge.
The “presence of any column larger than 512 bytes in the SELECT list, if UNION or UNION ALL is used,” prevents use of an in-memory temporary table. That means the temporary table will be created directly as MyISAM on the disk, and will keep the VARCHAR format, so it will be much smaller. Converting the field to TEXT would have the same effect.