maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #01331
New (by Bothorsen): Provide an " ORDER BY FIRST_JOIN.column" feature (59)
-----------------------------------------------------------------------
WORKLOG TASK
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
TASK...........: Provide an "ORDER BY FIRST_JOIN.column" feature
CREATION DATE..: Thu, 22 Oct 2009, 12:33
SUPERVISOR.....: Bothorsen
IMPLEMENTOR....:
COPIES TO......:
CATEGORY.......: Server-RawIdeaBin
TASK ID........: 59 (http://askmonty.org/worklog/?tid=59)
VERSION........: WorkLog-3.4
STATUS.........: Un-Assigned
PRIORITY.......: 60
WORKED HOURS...: 0
ESTIMATE.......: 0 (hours remain)
ORIG. ESTIMATE.: 0
PROGRESS NOTES:
DESCRIPTION:
Question:
How much effort would be required to provide an "ORDER BY
FIRST_JOIN.column" feature? We often do self-join queries and want to
order them by index, i.e.:
create table t1 (a int, b int, unique key 'by_a_b' (a, b)));
select * from t1 x inner join t1 y on (x.b = y.b) where x.a=1 and
y.a=2 order by (x|y).b desc limit 10;
but it is only ordered by index if you order by the first table in the
optimized join order, which could be either x or y depending on which
is more selective. we don't want to force the join order because
often one is far more selective than the other, but the only way to
know what table to order by then is to explain the query first. if
there was some way to tell mysql to just order by the first join in
the optimized order, that would help us. sometimes we do this across
tables too. both of these can be solved by rewriting the query to say
"using" instead of "on" in which case we don't have to specify the
table name of the column, just "order by b desc". but, we also want
to be able to do exclusions, in which case an "on" is required and
therefore we run into ambiguous column names:
Monty answered:
MySQL has an optimization where it knows that if x.b = y.b is used
then it can replace x.b with y.b and y.b with x.b in the WHERE
part
MySQL however doesn't do it for the ORDER BY part and I don't think
that should be very hard to do.
Question continues:
create table t2 (c int, b int, unique key 'by_c_b' (c, b)));
select * from t1 x inner join t1 y using (b) left join t2 on (t2.c =
3 and t1.b = t2.b) where x.a=1 and y.a=2 and t2.c is null order by (x|
y).b desc limit 10;
if we were ordering ascending, i think we could just leave off the
order by entirely in this case and it would happen to work since it's
reading in index order. but the combination of requiring an "on" and
descending sort leaves us unable to use these tricks. if we had
either an "ORDER BY FIRST_JOIN.column" or some way to tell the server
that 'b' is in fact joined as being equivalent across tables (except
where it may be null from left joining) and that we shouldn't have to
specify the table name at all, that would save us having to figure out
the table from the explain.
Monty answers:
It may be that the eq-replacment we have would solve this.
ESTIMATED WORK TIME
ESTIMATED COMPLETION DATE
-----------------------------------------------------------------------
WorkLog (v3.5.9)