Re: Optimization Suggestion: GROUP / ORDER on a connecting field in JOIN

Hi!

On Sep 14, Lorderon wrote:
> Hi...
> 
> There might be an unnecessary temporary file used when using GROUP or ORDER
> on a connecting field.
> I'll give an example:
> 
> Let's say I got 3 tables, with their primary keys:
> 1.    tbl_1 -> id1
> 2.    tbl_2 -> id2+another_id2
> 3.    tbl_3 -> id3+another_id3

...

> Now, I add GROUP BY on tbl_1.id_1, like this:
> SELECT *
>     FROM tbl_1
>         INNER JOIN tbl_2 ON tbl_1.id1=tbl_2.id2
>         INNER JOIN tbl_3 ON tbl_2.id2=tbl_3.id3 AND
> tbl_2.another_id2=tbl_3.another_id3
>     WHERE {where_condition}
>     GROUP BY tbl_1.id1;
> 
> If MySQL decides to go over the tables in the same order as before
> (tbl_2->tbl_1->tbl_3), then it uses a temporary table to group by tbl_1.id1,
> and the query takes longer to be executed (especially when LIMIT is also
> used).
> If I use GROUP BY tbl_2.id_2, then it doesn't use a temporary table, since
> it goes first over tbl_2.
> But... In this case:
>         GROUP BY tbl_1.id1 = GROUP BY tbl_2.id2 = GROUP BY tbl_3.id3
> since those fields are defined to be equal in the JOIN conditions... So why
> using a temporary table if I could not hit the exact connecting field of the
> first table MySQL goes over ???

You're right.
It's the optimization MySQL should be able to do.
It was not easy to do up until recently, but now he have reworked the
code that deals with these equality conditions so it should be easy to
do this optimization. And it should be done relatively soon.

(by the way, what you've reported isn't a bug, so it should've been sent
to internals@ not to bugs@ :)

Regards,
Sergei

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[email protected]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
       <___/  www.mysql.com

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]