Bug #4975 subselect (dependent) optimizer bug

• Submitted: 10 Aug 2004 14:56
• Modified: 18 Jun 2015 6:07
• Reporter: Gunnar von Boehn
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 4.1.3
• OS: Linux (Linux - probably any)
• CPU Architecture: Any
• Tags: performance, subquery

[10 Aug 2004 14:56] Gunnar von Boehn

Description:
Optimizer bug using subquery and IN.

The query uses the two tables (races) and (runs).

Problem:
 For the inner query the optimizer correctly used the index on column raceid. 
 But for the outer query is does not.

 Because of this the query takes hours instead of seconds.

 Changing the query from "IN (SELECT)" to using "JOIN (SELECT)" speed it up to one second.

I hope this description is helpfull
Kind regards
Gunnar

How to repeat:
Please see internal

Suggested fix:
please fix

[10 Aug 2004 14:58] Gunnar von Boehn

Oops. I just see that the flag stowstopper is set to true.
This is of course wrong. 

Sorry for that
Gunnar

[10 Aug 2004 14:59] Gunnar von Boehn

remove accidently set showstopper flag.

[16 Aug 2004 21:03] Dean Ellis

Changing to a feature request; already on our TODO.

[18 Jun 2015 6:07] Roy Lyseng

Fixed in 5.6 with implementation of materialized IN subqueries.