maria-discuss team mailing list archive

[Igor Babaev <igor@xxxxxxxxxxxx>]

Arjen Lentz wrote:
> Hi Mark
> 
> On 01/08/2009, at 3:15 AM, MARK CALLAGHAN wrote:
>> Why does UNION ALL use a temp table? Many users are surprised by this.
>> It would be faster to return rows from each branch of a UNION ALL
>> query directly to the network.
> 
> 
> Actually, it doesn't! I just tested it with a random MySQL 5.0
> If it's UNION ALL and no ORDER BY is used, no tmp table is used.
> So, it acts as you wish already.
> 
> I had this in my brain already, but verified by running a SELECT * FROM
> tbl UNION ALL SELECT * FROM tbl and running SHOW SESSION STATUS LIKE
> 'created_tmp_tables' before and after. No change.
> 
> If you run EXPLAIN on it, it'll create a tmp table, but that's an
> artefact of EXPLAIN rather than the query since the query itself is not
> actually executed then.
> 
> 
> Cheers,
> Arjen.

Arjen,

Set a breakpoint in select_union::send_data and you'll
see how a temporary table is filled in.

Regards,
Igor.