MySQL Bugs: #57146: SUB SELECT OPTIMIZATION

Bug #57146	SUB SELECT OPTIMIZATION
Submitted:	30 Sep 2010 15:58	Modified:	21 Mar 2011 4:21
Reporter:	Roberto Spadim (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	optimization, qc, sub select

Description:
hi guys,
select * from t1 where (t1.f1,t1.f2) in (select f1,f2 from t2)

today there´s many options to optimize this query, but
i want that (select f1,f2 from t2) should be a temporary table, and rewrited as:

select * from t1 where (t1.f1,t1.f2) in (('1','1'),('2','2') ... (table values) )

this could be implemented as sql extension like:

select SQL_BUFFER_RESULT f1,f2 from t2
or another SQL_BUFFER_RESULT like modification
or...

select SQL_TEMPORARY_SUB_SELECTS * from t1 where (t1.f1,t1.f2) in (('1','1'),('2','2') ... (table values) )

or... 
a mysql server variable "sql_mode" or another one...

How to repeat:
it´s a feature request

Suggested fix:
some implementations are allowed
sql compatible with other servers (via mysql server variable) or a sql extension, on sub-select or main select

i prefer sub select option, since some times we (developer) know that one table is small or bigger than other

maybe in future a automatic query optimizer could be used, but a hardcoded optimization could be allowed today =)

thanks guys

This is essentially a feature request for optimizer. There are many related/similar feature requests and worklogs. Please, check the following public worklogs:

http://forge.mysql.com/worklog/task.php?id=1117
http://forge.mysql.com/worklog/task.php?id=3808

and others. I think WL #3808 is the most close to what you requests here.

it's ok but they have a problem, they are trying a automatic method changing optimizer, i'm talking about a hardcoded method, and after in future a automatic optimizer option, but not for now
got?

what i do today...
create temporary table temporary_table_name select f1,f2 from t2;
select * from t1 where (f1,f2) in (select f1,f2 from temporary_table_name);
drop table temporary_table_name;

problem: temporary_table_name could be not droped if connection end, or a connection problem and a big time between queries, or anything else related with this, it's not a 1 query it's 3 queries procedure

could be replace by:

select * from t1 where (f1,f2) in (select SQL_BUFFER_RESULT f1,f2 from t2);
or
select * from t1 where (f1,f2) in (select SQL_BUFFER_SUBQUERY f1,f2 from t2);

problem: it's not compatible with standard sql, ok we will make a sql extension just for mysql optimizer...

or

set sql_mode='SQL_BUFFER_SUBQUERY';
select * from t1 where (f1,f2) in (select f1,f2 from t2);
again, it's 2 queries and not 1 query, but don't have temporary table don't being droped problem, but maybe a reconnection could be a problem...

the best one, is only one query

I would agree with this idea as a valid feature request, something useful until subquery optimizations will not be completely developed/backported to current trees.

But chances to see it implemented any time soon are probably low.

no problem :) 
there's some where that we can see the main lines of development? some times i have ideas and knowing if someone is working in a similar area is more interesting than putting a open idea =) it's easily developed =)

http://forge.mysql.com/worklog/ is the place, and http://planet.mysql.com/ plus public MySQL trees at launchpad, forums and mailing lists. 

MySQL User Conference should be even better place to find out what ideas exist and discuss them, but I had never been there, so can not tell you for sure :)

i will close since bug 24770 is similar to this and have work log