Temporary tables, union and union all

Temporary tables, union and union all: "Both UNION and UNION ALL use a temporary table to buffer results from all branches of the query. The temporary table buffers all rows before any are returned to the client. For UNION, a unique index is created on the table. For UNION ALL, a unique index is not created. The HEAP (MEMORY) engine is used as long as there are no LOB columns.

The HEAP engine uses fixed length rows. For variable length columns each row allocates the max size per column so a lot of memory can be wasted. When the temp table gets too big, it is converted to a MyISAM table. The switch is done when the size of the table is min(max_heap_table_size, tmp_table_size) (I think that equation is right, wouldn't it be nice if it were simpler?).

In some cases UNION ALL can be used to reduce network round trips when there are multiple SELECT statements that return similar columns: select * from foo UNION ALL select * from bar. While this reduces network latency, it can increase the load on the server from writing the temporary table.

A feature request might be open to not use a temporary table for UNION ALL. Search for more details on this topic.

What happened to the change to not allocate the max length for variable length columns in the HEAP engine? When will it appear in an official release? There is a feature request for it.

And now, a quiz from Domas. What is done for the following?

(select * from foo UNION ALL select * from bar) limit 10

PlanetMySQL Voting:
Vote UP /
Vote DOWN"