Simple Performance Question
Short prelude. I'm working on some web software that uses MySQL. Most of my expertise is with Perl, HTML, css, Javascript, etc., and very little with MySQL.
I have a few queries I've been working on trying to tune. My queries are fairly simple, and so my approach is fairly simple. I run a query I've found to be slow (on the order of 20 or 30 seconds when it needs to be instant), and by inspection I can usually see what I've done wrong, like the order of columns in the index or a missing index. My test table has about a half-million entries, enough for problems to show themselves.
I work interactively in a MySQL command line window. I run the query, verify it's slow by looking at the elapsed time reported at the end, I inspect it, deduce and make a fix to the table (almost always a change to table indices), then rerun the query.
My problem is that the queries I'm working on now run super-fast after the first time I run them. First time it takes maybe 20 or 30 seconds. Second time it takes maybe something like .373 seconds, and the same fast speed for all subsequent queries. So I go off and do something else for a while, and when I come back the query runs slow again.
I've got the query cache turned off, with query_cache_limit and query_cache_size both set to 0.
My question is, how do turn off whatever optimizing or learning MySQL is doing so that the slow performance for a query is as bad for later attempts as it is for the first?
--Ted