Unexpected join optimization

[Grace Zhu]

We have a custom storage engine to process the query execution but rely on 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 even.

It worked most of 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 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 has an explanation for this optimizer behavior? Even better if there's anything we can do to avoid this.

Thanks in advance.

Grace