query/result set caching

[Mark Modrall]

Hi...

Related to my UPDATE speed question, I was wondering about MySql's query/result set caching. More specifically, what things would get cached and what events would invalidate the cache.

If you enter a query in the query browser and execute it, the first time through it mysqld does all the work. If you just re-execute it, it comes back with the same result in 1/1000th of the time, so clearly both the query and the result set are cached.

1) is a parameterized, prepared query cached and can it reference the same result set (or subset) depending on the parameter?
I.e. if i have
select * from table where id > ?id
where id is the primary key, will mysqld keep the query and result set cached? Can it use it if ?id changes to narrow the subset?

For example, if my first execution has ?id = 1000000, the result set is for all rows with id greater than 1 million. If the next execution of the query has ?id=1100000 (a subset) would mysqld be able to solve from cache or would it have to re-run from scratch?

2) what events invalidate the query/result set cache and send you back to the drawing board? Inserts? updates?

I was having a problem with the .Net connector that causes me to re-execute my SELECT in increments because it's throwing Connection unexpectedly terminated errors processing every 1900 rows of the select. Obviously, there's a lot of overhead to re-doing the result set every 1900 rows and I'm trying to figure out how to improve the over all performance.

Thanks
_mark