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

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

Let's say running the next query (runs without a temporary table):

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};

And say that MySQL optimizes this query going first on the tables in this
order: tbl_2->tbl_1->tbl_3.
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 ???
(MySQL can decide to go over the tables in different sequences according to
the query itself and the tables datas, which means that I can not always hit
the exact connecting field of the first table MySQL goes over).


Any comments would be accepted gratefully...

-thanks, Lorderon.



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