How can I disable equality propagation in MySQL 5.0

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@google.com

--

MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/internals?unsub=lists@pantek.com Received on Tue Jul 17 23:13:23 2007

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek