----------------------------------------------------------------------- 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...: 5 ESTIMATE.......: 20 (hours remain) ORIG. ESTIMATE.: 20 PROGRESS NOTES: -=-=(Bothorsen - Fri, 18 Dec 2009, 16:21)=-=- Added the estimation time. Worked 5 hours and estimate 20 hours remain (original estimate increased by 25 hours). 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)