List:Internals« Previous MessageNext Message »
From:Zhixuan Zhu Date:April 17 2009 7:54pm
Subject:Unexpected join optimization
View as plain text  
Hi There,

We have a custom storage engine to process the query execution but rely
on the MySQL optimizer to give us the execution plan. We give the same
records info for all the tables and hope MySQL optimizer would treat all
tables equal.

It worked most of the time. But it looks like when the join column sizes
are different, the optimizer does not look at the from clause table
order any more. Instead, it looks at the left and right hand side of the
join filter.

Given l_orderkey is 8bytes column and p_partkey is 4bytes column, this
following query gives us the desired behavior, which lineitem is treated
as small side:

Select l_quantity, max(p_retailprice) from lineitem, part where
l_orderkey < 1000000 and p_partkey=l_orderkey group by l_quantity =
order by l_quantity;

By just flipping the join order, part is decided to be small side now
(open first), which is not ideal:

Select l_quantity, max(p_retailprice) from part,lineitem where
l_orderkey < 1000000 and l_orderkey=p_partkey group by l_quantity =
order by l_quantity;

Adding straight_join hint does not make any difference to either case.

Does anyone have an explanation for this optimizer behavior? Even better
if there's anything we can do to avoid this.

Thanks in advance.

Grace

Thread
Unexpected join optimizationZhixuan Zhu17 Apr
  • Re: Unexpected join optimizationIgor Babaev17 Apr
    • RE: Unexpected join optimizationZhixuan Zhu17 Apr