Hello Sergei,
>> The reason to do a merge is the case where we want the entire union sorted
>> equivalently to the component selects, especially when we only want the
>> first n, i.e.:
>
> Exactly.
> But note that even when component selects are not explicitly ordered, in
> the sense that there is no ORDER BY clause, you can still tell MySQL to
> order them as it'll enable you to do the k-way merge. Ultimately, it
> depends on the cost of sorting vs. the cost of using a temp table, and
> it's up to the optimizer to make the best choice.
I'm also getting confused between the current mysqld implementation,
and the current Eric Jensen implementation, but could you clarify --
my understanding is that the current mysqld (the one I can download!)
*always* uses a temporary table for every UNION query. Someone in the
optimizer team (not sure who) told me once that this wasn't so, but
then I went to the source and was still convinced it IS so, and the
optimizer team member then said "oh, you're right." But just in case
I'm wrong, I wanted to bring this up again in case it needs more
investigation. It sounds like you believe UNION doesn't always use a
temporary table, is that true?
Baron
--
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html
