List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:May 5 2001 12:22pm
Subject:derived tables?
View as plain text  

>>>>> "Braxton" == Braxton Robbason <robbason@stripped>
> writes:

Braxton> hi there.  I think it would be possible to implement derived tables in MySQL
Braxton> without too much difficulty, and it would make a lot of user problems easier
Braxton> to solve.

Braxton> There is a clear mapping between derived table queries and queries using
Braxton> temp tables, but in many applications, it is easier to use derived tables in
Braxton> a single pass of sql than to create multiple sqls and then to run them
Braxton> sequentially.  The algorithm I would use to convert derived table sql to
Braxton> temporary table sql is as follows:

Braxton> 1) does the query contain a derived table?
-> if no, then process it normally.

Braxton> 2) for each derived table sql: remove it and put it into a temporary table.
Braxton> the name of the temp table and any calculated columns (sum(x) etc) in it
Braxton> would have to be generated in such a way as not to conflict with user
Braxton> objects. Then recurse on these sqls in case they contain derived table sql
Braxton> in turn.

Braxton> 3) run all sqls in order. If there is an error, the standard error message
Braxton> wouldn't make much sense without some more complex processing.

Braxton> This could be implemented at a layer above the optimizer even.

Braxton> What do you think of this? Has there been an effort to implement derived
Braxton> table sql yet? I'd be happy to participate if that would help.

Braxton> Braxton

Exactly what do you mean with derived tables?

Sub selects like the following ?

SELECT * from table1 where salary= (select max(salary) from table1)

Yes, this could be done by creating a table for the sub-selects as
long as there aren't any reference to outer tables.

I would however like to delay this to 4.1 when we will start coding
the base to handle optimized sub-select queries.
(This is actually not that hard, we would just need find a couple of
weeks of development time...)

derived tables?Braxton Robbason5 May
  • derived tables?Michael Widenius5 May
    • RE: derived tables?Braxton Robbason5 May
      • RE: derived tables?Michael Widenius6 May