Clearing the cache

[nb1341]

Hi!

I'm trying to compare the query times for two different table setups. The problem is that when I run the first query, 'something' is cached, so that the second query comes back much quicker. The query cache is already turned off. It seems that the cache persists even across mysql server restarts.

Is there a way to make sure both queries are run with the same starting conditions?

I'm using MySQL 4.0 on Linux (Red Hat 8).

Example of the queries:

query 1:
select t1.a, t2.b
from t1, t2, t3, t4, t5
where t1.i=t2.i and t2.j=t3.j and t3.k=t4.k and t4.l=t5.l and t5.x=nnn

query 2:
select t1.a, t2.b
from t1, t2, t3, t4, t6
where t1.i=t2.i and t2.j=t3.j and t3.k=t4.k and t4.m=t6.m and t6.x=nnn

The difference is that I'm using t5 in one query and t6 in the other.

Your help is greatly appreciated.

[greenman]

You can clear the query cache with "RESET QUERY CACHE".

You can also use something like "SELECT SQL_NO_CACHE f1,f2 FROM t1" to avoid using the query cache. However, you are mistaken about the query cache being used in your example - the queries must be byte-for-byte identical in order to make use of the query cache. Shutting down the server will also clear any other caches that may be lurking.

[nb1341]

greenman,

Thank you for your reply, but I already know that the query cache is NOT being used, as it's turned off in the configuration file. There's some other cache at work here (OS/disk caching?) that persists even across mysql server restarts.