← Back to team overview

maria-developers team mailing list archive

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
COPIES TO......: 
CATEGORY.......: Server-RawIdeaBin
TASK ID........: 59 (http://askmonty.org/worklog/?tid=59)
VERSION........: WorkLog-3.4
STATUS.........: Un-Assigned
PRIORITY.......: 60
ESTIMATE.......: 0 (hours remain)




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

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.


WorkLog (v3.5.9)