List:General Discussion« Previous MessageNext Message »
From:Iikka Meriläinen Date:August 29 2002 3:16pm
Subject:RE: performance tunning 4.x
View as plain text  
Hello,

A MySQL developer would be able to answer to these questions more
accurately, but I'll try to answer _logically_. So don't bet a million
dollars on the correctness of my answer.

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

I think this flushes the cache. Think about a situation where col1 is a
computed column that derives its values from the data in col3. When col3
is updated, the value of col1 in the query cache would become invalid.

> 2) or will the cache be flushed if someone changes a row that falls outside
> of the query's Where clause:

This shouldn't explicitly flush the cache, but I don't know the details of
the implementation. This insert shouldn't have any effect on the
aforementioned SELECT query, because col1 here is 'D'.

> 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
>
>

******************************************
* Iikka Meriläinen                       *
* E-mail: Iikka.Merilainen@stripped *
* Vaala, Finland                         *
******************************************

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