List:General Discussion« Previous MessageNext Message »
From:mos Date:August 29 2002 2:45pm
Subject:RE: performance tunning 4.x
View as plain text  
>
>Hi,
>
>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.
>
>
>Regards,
>Iikka

likka,
         I read it too but I have a question about the cache, this sentence 
in particular.

 >>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 
of intelligence.)
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?

MONTY:
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.

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