Re: Intermediate Query Results

[Øystein Grøvlen]

Ashwini Singh Wrote:
-------------------------------------------------------
> Is it possible to access the intermediate results
> generated by mysql during execution of a query??

Not directly, but you could of course split the query into several queries and store the intermediate results in a temporary table.

Note that unless an implicit temporary table is used in the query plan (e.g., for sorting or grouping), intermediate results are not actually materialized.

> Creating a view for the query stores the results
> of the query, i need to store the intermediate
> results of the every query for further query
> optimization. I couldn't find any way out for
> this.
>
> There is another point i want to extract. The
> candidate plans for which mysql finds the costs
> and selects the best plan.

Optimizer trace has been created for exactly this purpose. (http://dev.mysql.com/doc/internals/en/optimizer-tracing.html).

>
> I used the keyword "EXPLAIN", but it gives the
> execution plan that mysql has used to execute the
> query(considering its minimum cost), but the
> requirement is to find all the plans that mysql
> has considered before selecting the best one.

This sounds like a student project. What is the goal of this project, and would it be possible for you to share the result?

>
> I know its a very broad question but i couldn't
> find anything in support of the task i want to do.

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway