Re: sql performance and cache

On Sat, Oct 11, 2003 at 10:43:04AM +0100, Chris Faulkner wrote:
> I have two very similar queries which I need to execute. They both
> have exactly the same from / where conditions. When I execute the
> first, it takes about 16 seconds. The second is executed almost
> immediately after, it takes 13 seconds. In short, I'd like to know
> why the query result isn't being cached and any ideas on how to
> improve the execution.

The way to do the type of caching you're talking about, if i
understand you correctly, would be to create a temporary
table. Specifically, create a temporary table with the results of the
second query. Then run a select * on that table (with no where
clause), and follow it with a select max(replace(...)) on the same
table (no where clause).

That guarantees two things:

1- The joins/filters are not parsed and evaluated twice, with the
corresponding disk reads.
2- The data is exactly consistent between the two queries.

Correct me if i misunderstood your problem.

-johnnnnnnnnnn