Measure execution time

[sunaj]

Hi,

I would like to measure the execution time of my queries in order to evalutate the performance in/decrease for different changes.
Even if I use 'sql_no_cache' the query executes MUCH faster the second time I run it, and is therefore not representative of the true performance. How to I avoid this problem?

I'm using MySQL 4.1 on a windows server 2000.

I be glad to get an explanation to why the query executes faster the 2nd time.

I'll be happy to supply more detail...

thx in advance

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[TonyGroves]

RESET QUERY CACHE will remove all queries from the cache. The config-file option "query_cache_size=0" will prevent a cache from being used at all. I don't know why your "SELECT SQL_NO_CACHE ..." doesn't work.

 

[sunaj]

Hi Tony,

RESET QUERY CACHE makes no difference. I'm pretty sure that it does not use the query cache, because if I turn it on the query executes in 0 ms (with sql_no_cache it's ~100 ms).
The first time the query is executed (e.g. if I change the where clause) it executes in ~3500 ms e.i. 35 times slower!
I've also tried to put the indexes in a separate Key_cache, which I can empty, but that also makes no difference.


Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'