Re: slow query execution

[Richard Huxton <dev(at)archonet(dot)com>]

Trigve Siver wrote:
> ----- Original Message ----
> From: Richard Huxton <dev(at)archonet(dot)com>
> To: Trigve Siver <trigves(at)yahoo(dot)com>
> Sent: Thursday, May 31, 2007 10:33:40 AM
> Subject: Re: [SQL] slow query execution
>
> > So - you want something like:
> >
> > The user runs a query ("all blue things") and that gives a list of 
> > results. They can then filter those results further ("shape=round") and 
> > you want to highlight those elements that match.
> >
> > You either can't or don't want to filter in the application, rather you 
> > would like to run this as two queries but need to match up results from 
> > the second query with the first query (your list).
> >
> > Suggestion:
> >
> > For the first query, make sure you have the relevant primary key columns 
> > in your query and do:
> >   CREATE TEMPORARY TABLE my_results AS SELECT ...
> > Then, you can join against that table in the second query. The temporary 
> > table will exist until you disconnect - see CREATE TABLE for details.
>
> I think that I can use temporary tables with my previous soultion. As you mentioned,
> I can create temp table with the select ("all blue things")[main select]. (I think I can also add row_numbers 
> to each record as I want to jump to first record in my list which satisfy ("shape=round") 
> condition) When ("shape=round") Query will be made and the: 
>
> a)I can make join in this the query with my temp table (as you mentioned)
>
> b)I can make this query on temp table. But the temporary table haven't indexes.

You can add indexes if you want them. You'll also want to run analyze 
against the temp table.

> But when main select is some complicated select then (b) can be used.
>
> thanks
>
> Trigve


--
  Richard Huxton
  Archonet Ltd