Bug #4975 subselect (dependent) optimizer bug

Submitted: 10 Aug 2004 14:56 Modified: 18 Jun 2015 6:07
Reporter: Gunnar von Boehn Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:4.1.3 OS:Linux (Linux - probably any)
Assigned to: 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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.053 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.