MySQL 5.0 added equality propagation to the optimizer. This has made
some of my queries much slower, both the cost of optimization and the
resulting plans. The problem occurs for queries with large inlists.
For example:
select * from t1, t2 where t1.a = t2.a and t1.a in (<large_in_list>)
and t2.b between Const1 and Const2
In this query, the optimizer appears to be adding: t2.a in
(<large_in_list>) and this is done despite the size of the inlist.
Many of my queries have thousands of values in these inlists. I know
that predicates are being added because handler::records_in_range is
called on t2 by check_quick_keys() once for each value in the inlist.
I want to disable equality propagation for large inlists, but I have
yet to figure out how to do this. I have disabled it for tables for
which there is a FORCE INDEX hint (done by adding code to
check_simple_equality), but it is too much work to add hints to all
queries with large inlists. I have added code to
build_equal_items_for_cond() to disable calls to check_equality() for
items that are large inlists, but that does not work. I need to either
prevent propagation somewhere else in the code or do something in
build_equal_items_for_cond() when the join condition is processed.
Can someone point me to the right place in the code?
I have a support ticket open for this, but that has yet to resolve my problem.
--
Mark Callaghan
mcallaghan@stripped
