Hard to debug slow queries

caching makes the process impossible

[Elric99]

Hello,

I'm using mysql slow query log to find problem queries.

The trouble is after one search in PHPMYadmin the result seems to be cached. Subsequent searches appear in .002 seconds.

Adding SQL_NO_CACHE doesn't appear to have any effect.

Is there any way to reliably recreate sloq queries?

Thanks

Tom

[whoisgregg]

The query cache will not work if any part of the SELECT is non-deterministic. So, if you use a function like RAND() in your select expression, then that query will never be cached:

SELECT *, RAND() AS cache_buster FROM ...

This shouldn't have a dramatic affect on your overall execution time, nor should it throw off MySQL's other built-in optimizations. Since you can see that the value is changing on each reload, it gives you the confidence that you are not hitting the cache.

[Elric99]

Thanks whoisgregg

Oddly I can't get this to work. Mysql is still reading from the cache for anything other than my first query.

Is there anything else I could try?

[whoisgregg]

If the value shown in the `cache_buster` column stays the same on refreshes, then you must be pulling from some other cache... either your browser cache or some caching perhaps at the PHP layer?

If the value shown in the `cache_buster` column is changing but the overall execution time of the search seems curiously faster on subsequent refreshes, than what's probably happening is that the key cache [dev.mysql.com] is now primed (for lack of a better word).

Which is occuring?

[Elric99]

"If the value shown in the `cache_buster` column is changing but the overall execution time of the search seems curiously faster on subsequent refreshes, than what's probably happening is that the key cache [dev.mysql.com] is now primed (for lack of a better word).

Which is occuring? "

This is the scenario I am seeing. Changing rand number but second query is faster.

[whoisgregg]

You can force the key cache to be restructered by changing the size of the key_buffer_size. These queries change it to 2MB and then restores it to it's default value:

SET GLOBAL key_buffer_size = 2097152; SET GLOBAL key_buffer_size = DEFAULT;

Unfortunately, to really make sure you've destroyed every possible cache layer you practically have to reset your server. :/

If this doesn't help, maybe looking at the query itself will reveal another avenue to explore.