Reason for 6x perfomance increase in data fetch when caching disabled?

I'm running MySQL 5.5 on windows and I'm trying to optimize a query. However, I can't seem to make any progress because when I baseline it I get 131s, but if I re-execute it I get 23s. If I wait awhile (like 10 minutes or so) it will go back to 131s, but I never know if it's gone back until I execute it. So I can't really figure out if my optimizations are helping. Of course, I assumed it was caused by the query cache, so turned that off but I'm still getting the same results.

开发者_如何学GoIt's a select with several inner joins and a couple outer joins. Two of the tables in the inner join are every large, but it's generally joining on indexes. There are a couple of "in" statements in the joins.

So, my question is, what would cause this change in response time? Execution plan caching? OS file caching? Index caching? Something else?

Thanks you!

Edit: Here is the the query and table sizes:

select SQL_NO_CACHE count(1)

from reall_big_table_one ml

inner join pretty_big_table_one ltl on ml.sid=ltl.sid

inner join pretty_big_table_two md on ml.lid=md.lid

inner join reference_table ltp on ltl.ltlp_id=ltp.ltlp_id

left join pretty_big_table_three o on ml.sid=o.sid and o.cid not in (223041,226855,277890,123953,218150,264789,386817,122435,277902,278466,278430,277911,363986,373233,419863) and o.status_id in (100,400,500,700,800,900,1000)

left join medium_table ar on o.oid=ar.oid and ar.status_id in (1,2)

where ml.date_orig >= '2011-03-01' and ml.date_orig < '2011-04-01' and ml.lid=910741

ml has 50M rows

tlt has 1M rows

md has 1M rows

tlp has 800 rows

o has 7M rows

ar has 25K rows

The operating system is caching the drive the second time? If you run an exe twice it will come up far more quickly the second time too.

The db server isn't the only thing that caches data. The operating system has it's own caching system as does the hard drive you're accessing. That's the reason you're seeing performance increases on subsequent calls.

To establish a baseline you could go from cold booting to running the query. This is a tedious process (I've done it myself on large, complex queries) but a cold boot will make certain that no data is cached. You can also simply throw out the first query, run ten or more of your test queries back to back and take the average as your performance benchmark. You can also run the query many more times in sequence, even after cold boot and take the average. This is a good idea anyway to see what changes under different circumstances such as page faults, spikes from other applications (which you would want manually replicate) etc.