MySQL Bugs: #54231: Performance Issue with subselects

Bug #54231	Performance Issue with subselects
Submitted:	4 Jun 2010 11:53	Modified:	11 Jun 2010 15:43
Reporter:	Christoph Moser	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S5 (Performance)
Version:	5.2.22	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	performance, subquery, where in

Description:
The select

SELECT field_1 FROM `db_1`.`table_1` WHERE field_1 IN (SELECT field_1 FROM `db_1`.`table_1` where field_2 = '1' and field_3 = 'xy');

takes several minutes when searching in a table with about 3000 rows.

How to repeat:
In my case the performance for this select is always bad.

Here is the exact duration time: 985.131 sec / 0.000 sec

Please show output from:
EXPLAIN SELECT field_1 FROM `db_1`.`table_1` WHERE field_1 IN (SELECT field_1 FROM `db_1`.`table_1` where field_2 = '1' and field_3 = 'xy');

Also my question is ...
Do it need as long when you just will take plane cli?

The same performance issue occures with mysql-connector-odbc-5.1.6-win32!

Output you wanted:
EXPLAIN SELECT max(Rev) FROM `e3Build`.`SvnRevisionInfo` WHERE Rev IN (SELECT Rev FROM `e3Build`.`SvnRevisionInfo` where isBuildable = '1' and Solutionname = 'Workspace.sln')	2 row(s) returned	0.000 sec / 0.000 sec

I do not really know what you are wanted before - so there are the results from the select starting with explain:

	1|PRIMARY|SvnRevisionInfo|ALL|null|null|blob|null|23774|Using|where
	2|DEPENDENT|SUBQUERY|SvnRevisionInfo|ALL|null|null|blob|null|23774|Using|where

Looks like you have a Cartesian join here, for the table with 20000 rows or so, and this is the reason of the problem.

Try the same query from mysql command line client. If it will be also slow this is definitely has nothing to do with Workbench.

Also, please, send the results of:

show create table `db_1`.`table_1`;

1) show create `db_1`.`table_1`;
     >> Result: Table = BLOB, Create Table = BLOB
     >> Output: 1 row returned, 0.000 sec / 0.000 sec

2) With command-line-client the above mentioned select takes approximately the same time

Where should I post the bug instead of here? The given categories when reporting a bug are not very descriptive without competent knowledge of mysql...

This is the only proper place to report bugs for community users. 

As execution time is the same with mysql command line client this is NOT a bug in Workbench for sure. Also, as you just require server to read too many rows, I see no server bug here.