List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:August 30 2002 4:30pm
Subject:Re: RE: performance tunning 4.x
View as plain text  
At 11:04 -0500 8/30/02, mos wrote:
>At 07:58 AM 8/30/2002, you wrote:
>>mos,
>>Thursday, August 29, 2002, 5:45:05 PM, you wrote:
>>
>>>>Start by looking at the MySQL documentation:
>>>>http://www.mysql.com/doc/en/Query_Cache.html
>>>>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.
>>
>>m>          I read it too but I have a question about the cache, 
>>this sentence
>>m> in particular.
>>
>>m>  NOTE: The query cache does not return stale data. When data is modified,
>>m> any relevant entries in the query cache are flushed.
>>
>>m> If I have a select statement like:
>>
>>m> select col1, col2 from table1 where col1='A'
>>
>>[skip]
>>
>>m> c) Or will any update to the table automatically flush the cache?
>
>Victoria,
>
>>Yes, relevant entries in the query cache are flushed when any 
>>changes were made on the
>>table.
>
>Can you define "relevant"? Do you mean all caches for that table?

Queries in the cache that use the table that has been changed.

>
>Are you saying any change at all to the table, say I add a row, 
>delete a row, or modify a row even if it is not in the scope of the 
>cached query, will cause all caches for that table to be discarded? 
>Argghhh! :-O

Yes.  How will the server know that the row is in the scope of the query
unless it reruns it again?  If you change the thing from which the cached
result is derived, the cache becomes invalid.

How would you do it otherwise?

>
>Example:
>
>select customer_name from customers where state = 'NY'  //<-Adds 
>query to cache
>
>insert into customers (customer_name, state) values('jones', 'CA') 
>//<-Flushes the cache for customers table?
>
>select customer_name from customers where state = 'NY'  //<-No 
>longer in cache?
>
>If you are correct, then I feel caching is useful primarily for 
>readonly access to a table. As soon as a row is modified in any way, 
>the caches for that table are flushed. Would you agree?

Yes.  How would you suggest that it be done otherwise?

>
>Mike

Thread
performance tunning 4.xSean Hager28 Aug
  • Re: performance tunning 4.xIikka Meriläinen28 Aug
    • RE: performance tunning 4.xSean Hager28 Aug
      • RE: performance tunning 4.xIikka Meriläinen28 Aug
        • RE: performance tunning 4.xmos29 Aug
          • Installing mysql 4.0.3 with linux threads on freebsdVarshavchick Alexander29 Aug
            • Re: Installing mysql 4.0.3 with linux threads on freebsdDan Nelson29 Aug
              • Re: Installing mysql 4.0.3 with linux threads on freebsdVarshavchick Alexander30 Aug
            • limiting the userSoheil Shaghaghi30 Aug
              • limiting the userSoheil Shaghaghi1 Sep
                • RE: limiting the userDean Harding1 Sep
          • RE: performance tunning 4.xIikka Meriläinen29 Aug
          • Re: RE: performance tunning 4.xVictoria Reznichenko30 Aug
            • Re: RE: performance tunning 4.xmos30 Aug
              • Re: RE: performance tunning 4.xPaul DuBois30 Aug
              • Re: Re: RE: performance tunning 4.xVictoria Reznichenko31 Aug