>Start by looking at the MySQL documentation:
>Read the entire 6.9 section.
>It offers quite a bit of information on how the cache operates, how
>you should configure it and so on.
I read it too but I have a question about the cache, this sentence
>>NOTE: The query cache does not return stale data. When data is modified,
any relevant entries in the query cache are flushed. <<
If I have a select statement like:
select col1, col2 from table1 where col1='A'
1) will the cache be flushed if someone changes a non-referenced column for
one of the retrieved rows:
Update table1 set col3 = 'x' where col1='A'
this update changes col3 that falls within the Where of the query but it
affects a column that is not returned by the query.
2) or will the cache be flushed if someone changes a row that falls outside
of the query's Where clause:
insert into table1 (col1, col2, col3) values('D', 'x', 'y');
This last query updates the same table as the select query, but the row
that is added is outside the range of the query's where clause.
So how smart is the cache?
a) Is it smart enough to allow table updates and if the update doesn't
affect the query, will it continue to use the cache? (This requires a lot
b) Or will any update to the rows that fall within the range of the query
(regardless of whether the column is in the query) will flush the cache?
c) Or will any update to the table automatically flush the cache?
I would like to see the Explain command to say if it is using the cache or
not (CACHE:Yes/No). This would help developers make better use of the cache
if they know how what affects it.