MySQL Bugs: #10312: subquery cant be optimized correctly

Bug #10312	subquery cant be optimized correctly
Submitted:	2 May 2005 11:51	Modified:	14 Jan 2020 21:56
Reporter:	Corin Langosch	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.4	OS:	Linux (linux debian)
Assigned to:		CPU Architecture:	Any

Description:
using a subquery in the where clause is much slower than running the subquery, building a list and running the main query with this list instead of the subquery. explain shows that when using the subquery, mysql doesn't use the index in the outer query while it is used when the raw list (exactly the same, the subquery returns) is given directly.

How to repeat:
query with subquery (SLOW, time: 2.74113s)
SELECT u.user,u.sex,u.geburt_age FROM user AS u LEFT JOIN user_profil AS up ON (u.id=up.owner_id) LEFT JOIN user_foto AS f ON (u.id=f.owner_id) WHERE u.land_id=1 AND u.stadt_id IN (SELECT s2.id FROM geodata.stadt AS s1,geodata.stadt AS s2 WHERE s1.id IN (17506) AND s1.kreis_id=s2.kreis_id) ORDER BY u.user
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1 	PRIMARY 	u 	ALL 	land_stadt 	 	 	 	115107 	Using where; Using filesort 
1 	PRIMARY 	up 	eq_ref 	PRIMARY 	PRIMARY 	4 	findmich_conv.u.id 	1 	Using index 
1 	PRIMARY 	f 	eq_ref 	PRIMARY 	PRIMARY 	4 	findmich_conv.u.id 	1 	Using index 
2 	DEPENDENT SUBQUERY 	s1 	const 	PRIMARY,kreis_id_name 	PRIMARY 	4 	const 	1 	 
2 	DEPENDENT SUBQUERY 	s2 	eq_ref 	PRIMARY,kreis_id_name 	PRIMARY 	4 	func 	1 	Using where 

subquery alone (fast, time: 0.00052s):
EXPLAIN SELECT s2.id FROM geodata.stadt AS s1,geodata.stadt AS s2 WHERE s1.id IN (17506) AND s1.kreis_id=s2.kreis_id
explain for 'SELECT s2.id FROM geodata.stadt AS s1,geodata.stadt AS s2 WHERE s1.id IN (17506) AND s1.kreis_id=s2.kreis_id
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1 	SIMPLE 	s1 	const 	PRIMARY,kreis_id_name 	PRIMARY 	4 	const 	1 	 
1 	SIMPLE 	s2 	ref 	kreis_id_name 	kreis_id_name 	4 	const 	45 	 

query with data from subquery (fast, time: 0.01267s):
EXPLAIN SELECT u.user,u.sex,u.geburt_age FROM user AS u LEFT JOIN user_profil AS up ON (u.id=up.owner_id) LEFT JOIN user_foto AS f ON (u.id=f.owner_id) WHERE u.land_id=1 AND u.stadt_id IN (17506,17507,17508,17509,17510,17511,17512,17513,17514,17515,17516,17517) ORDER BY u.user
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1 	SIMPLE 	u 	range 	stadt_user,land_stadt 	land_stadt 	8 	 	269 	Using where; Using filesort 
1 	SIMPLE 	up 	eq_ref 	PRIMARY 	PRIMARY 	4 	findmich_conv.u.id 	1 	Using index 
1 	SIMPLE 	f 	eq_ref 	PRIMARY 	PRIMARY 	4 	findmich_conv.u.id 	1 	Using index 

Suggested fix:
i'm not sure what the error , because also FORCE INDEX (land_stadt) doesnt speed up the query with the subquery. but it's obious there must be something wrong with mysql here ;)

Hi,

Can you please upload the table schema of the tables used in these queries ?

Thanks !

Hello,

This query is related to the same problem as bug report #10309 that you submitted before. I mark this bug report as "To be fixed later" too.

The provided subquery should be handled by new subquery optimizations in MySQL 6.0. 

Corin, could you please check if the query runs faster on MySQL 6.0? MySQL 6.0 is not production yet, but now it is time we could still fix the optimizer if the query is not handled. Alternatively, you could attach the table data to the bug and we will make sure the query will be fast in 6.0.

Please, check with MySQL 6.0.7 and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Posted by developer:
 
This is likely fixed with semi-join optimizations in 5.6.