Bug #27794 subqueries optimizations

• Submitted: 12 Apr 2007 21:14
• Modified: 5 Apr 2011 5:48
• Reporter: Roberto Spadim (Basic Quality Contributor)
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: any
• OS: Any (any)
• CPU Architecture: Any
• Tags: qc

[12 Apr 2007 21:14] Roberto Spadim

Description:
from bug: 27773 
i have a big querie that return many subqueries (see how to repeat)

since value1,value2,value3, ... ,value_n are one column from table2 and at the same row (the subquerie just return one row)

could mysql optimize the queries to something like explained in suggested fix?

i know that's not a default sql command, since each subqueries should return only one row and one column for each column and row of primary query

but we could return many columns from only one row on subqueries, today the only problem is aliasing each value to one column name

i don't know if mysql optimize all subqueries as one subquerie but if we execute the query with "EXPLAIN" the optimizer result the number of subqueries + 1 rows (i think that optimize execute many subqueries to return the value that's on same row as the other subquerie)

How to repeat:
select value_from_table_a,
(select value1 from table2 where primary_key=a.primary_key) AS value1,
(select value2 from table2 where primary_key=a.primary_key) AS value2,
(select value3 from table2 where primary_key=a.primary_key) AS value3,
....
(select value_n from table2 where primary_key=a.primary_key) AS value_n
from table1 as a
where where_clausule

Suggested fix:
select value_from_table_a,
(select value1,value2,value3,...,value_n from table2 where primary_key=a.primary_key) AS (value1,value2,value3,...,value_n)
from table1 as a
where where_clausule

[26 Apr 2007 10:16] Valeriy Kravchuk

Thank you for a reasonable feature request.

[18 Aug 2007 2:25] Igor Babaev

- This is a request for an optimization that does not look to be widely applied.
- Yet an implementation of this feature will take 2 man-months.

By the above reasons I move the bug to 'To be fixed later'.
Product management will decide in what version a fix for this problem appears.

[21 Mar 2011 4:05] Roberto Spadim

i'm reporting again to check any news.
i'm working around it with a create table before and a drop table after

[5 Apr 2011 5:48] Roberto Spadim

does key cache or another cache help here?
maybe it's not a 'problem' just a cache tweak