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: | |
Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
Version: | 5.5 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | optimization, qc, sub select |
[30 Sep 2010 15:58]
Roberto Spadim
[30 Sep 2010 16:19]
Valeriy Kravchuk
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.
[30 Sep 2010 16:55]
Roberto Spadim
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
[30 Sep 2010 17:30]
Valeriy Kravchuk
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.
[30 Sep 2010 17:40]
Roberto Spadim
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 =)
[30 Sep 2010 18:00]
Valeriy Kravchuk
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 :)
[21 Mar 2011 4:21]
Roberto Spadim
i will close since bug 24770 is similar to this and have work log