Bug #30342 Equality propagation can make optimization much slower
Submitted: 9 Aug 2007 20:52 Modified: 28 Oct 2007 15:27
Reporter: Mark Callaghan
Status: Analyzing
Category:Server: Optimizer Severity:S5 (Performance)
Version:5.0.37 OS:Any
Assigned to: Valeriy Kravchuk Target Version:
Tags: propagation, equality, performance, Optimizer

[9 Aug 2007 20:52] Mark Callaghan 
Description:
For queries like:

select * from t1, t2 where t1.a = t2.b and t2.b in (<large-in-list>)

equality propagation can enable the use of the t2.b in (<large-in-list>) predicates to be
used for an index access path on t1.a assuming there is an index on t1.a. This can result
in many calls to handler::records_in_range for t1.a. When the handler does RPCs to a
remote server (as NDB might and as custom storage engines do), then the optimizer can
spend too many seconds determining a query plan because of predicates that were not in
the query as written.

I think that the optimizer should have be more restrained when creating predicates that
weren't in the query as written. Mikael Ronstrom suggested that the NDB engine might
display this problem because.

How to repeat:
Create two tables with engine=NDB with an index on t1.a as described above.
[9 Aug 2007 20:54] Mark Callaghan 
This originated with a customer bug, but I won't provide a testcase for that as it
requires a storage engine for which handler::records_in_range is expensive, and I don't
use NDB.