On 07.07.11 12.05, Sergey Glukhov wrote:
> Hi Roy,
> On 07/05/2011 11:38 AM, Roy Lyseng wrote:
>> Hi Gluh,
>> The patch in itself makes sense and is hereby approved.
>> However, I think that you should file a new bug for longer-term cleanup of
>> this feature. As far as I can see, the information requested is still not
>> aggregated in the most appropriate place.
> I would prefer to add TODO comment instead,
> let me know if it's ok.
That's fine. Please make sure that sufficient information goes into the @todo.
>> The basic problem here is that used table information from different
>> select_lex objects are not compatible and should not be aggregated into the
>> same field.
>> I think that a correct fix would look something like this:
>> - Aggregate used tables information for selected expressions into the
>> select_lex object where the
>> tables are referenced in the from clause.
>> - If there is no such select_lex object (for single-table insert, update and
>> delete), aggregate the
>> information on the LEX object instead.
> The only statement where used_tables info also is used is INSERT(mysql_insert()
> select_lex for INSERT is always created. So we could use select_lex object.
>> - This information should be sufficient for proper optimization of a UNION
>> query, such as:
>> SELECT t11.a, t11.b FROM t11 JOIN t12 USING(c1)
>> SELECT t21.a, t22.b FROM t21 JOIN t22 USING(c2);
>> In the current code, the information aggregated into lex->used_tables is
>> This is correct for the second query of the UNION, but incorrect for the first
>> Hence, the desired optimization is not carried out for the first query.
>> - The proposed change also means that used tables information is propagated
>> correctly also for
>> subqueries, but notice that the general subquery optimizations are more useful
>> than this optimization
>> in this case.
>> - For semijoin transformation, it is not necessary to propagate used tables
>> from subqueries into the parent level.