How to clear MySQL "memory"?

[mlse]

Hi all,

I'm using version 5.1 on Debian Etch.

I am currently fiddling with my DB architecture and queries on my development machine and I am trying to find the fastest way to do what I want to do.

The trouble is that MySQL seems to be remembering my queries. E.g. the first time I enter a query, it is executed in 3.4 seconds (for example). When I re-run the same query, it is executed immediately! (0.0 seconds is reported). Another similar query is reported as running in 0.0 seconds too ... but when I shut down and re-start MySQL, the second query is reported as running in 5.2 seconds (or whatever) or the first query runs in 3.4 seconds again.

The point is that it would seem that MySQL is remembering my results/doing something clever to optimise itself in order to execute my queries as fas as possible.

*BUT* I'd like to be able to switch that behaviour off so that I can measure the time it takes to execute a query without optimisation, because that will give a "worst case" measure of query speed (which is important for getting a true measure of the efficiency of my queries!).

[tomws]

You may be looking for information on the query cache. I don't bother with it myself, so I'm not sure what you can do with it, but maybe this link (http://dev.mysql.com/doc/refman/5.1/en/query-cache.html) will get you started in the right direction.

[mlse]

Excellent, that's a great starting point.

[Old Pedant]

Don't forget that you *could* be encountering disk file system caching, too. Dunno abou Debian, but some OS's keep a cache of the last NN "pages" fetched from disk, and so if you go fetch those same pages--whether from MySQL or from you application program--you get better times on the subsequent hits if that page is still in the cache.

This phenomenon is more likely to occur in larger OS's (e.g., Windows Server) than in small systems, but there's no reason that any OS might not exhibit it.