sql qurey_cache

[Alexandre Jaquet]

Hi when I read the documentation about how the query it's doesn't clear
my mind about how it can optimize db quering.

When I include a SQL_CACHE clause in a query, does the datas are stored
in memory or not ?

If for exemple I've a series of queries :

SELECT [fields] FROM [db] WHERE [statement= value1]
SELECT [fields] FROM [db] WHERE [statement= value2]
SELECT [fields] FROM [db] WHERE [statement= value3]
SELECT [fields] FROM [db] WHERE [statement= value4]

the only things who change are the statement, any optimisation is
possible by adding "SELECT SQL_CACHE" ?

In the documentation I read :
" Queries are compared before parsing, so the following two queries are
regarded as different by the query cache:"

In my example does caching by adding SQL_CACHE are done for each queries ?

thx for your response

[Bill Karwin]

Re: sql qurey_cache

Alexandre Jaquet wrote:[color=blue]
> When I include a SQL_CACHE clause in a query, does the datas are stored
> in memory or not ?[/color]

Sometimes.

If I understand the documentation correctly, you must set the system
variable query_cache_siz e. This is the amount of memory mysql allocates
for the query cache. It is zero (0) by default, which effectively
disables query caches. You should set this variable.
[color=blue]
> the only things who change are the statement, any optimisation is
> possible by adding "SELECT SQL_CACHE" ?[/color]

It depends on the expression used in the statement, and also it depends
on whether the table is updated between the times when you execute your
cached queries.
[color=blue]
> In the documentation I read :
> " Queries are compared before parsing, so the following two queries are
> regarded as different by the query cache:"[/color]

There are other conditions that cause the cached query to be discarded.
For example, any INSERT, UPDATE, or DELETE to a table causes its
cached queries to be invalidated and the queries' results are removed
from the cache.

There are also several types of query expressions that prevent a query
from being cached. Read the documentation for details.

Regards,
Bill K.

[Good Man]

Re: sql qurey_cache

Bill Karwin <bill@karwin.co m> wrote in news:d07p38030e 8@enews1.newsgu y.com:

[color=blue]
> Regards,[/color]

please don't ever disappear from this newsgroup! i'm sure i will have a
question for you some day, and your responses to others have always been
incredible!!!

[Bill Karwin]

Re: sql qurey_cache

Good Man wrote:[color=blue]
> please don't ever disappear from this newsgroup! i'm sure i will have a
> question for you some day, and your responses to others have always been
> incredible!!![/color]

Thanks!! I like doing it. Answering people's questions leads me to
research areas of MySQL that I don't normally use. It's a great way to
learn. One of these days I should be ready to take their certification
exams.

Regards,
Bill K.

[steve]

Re: sql qurey_cache

"Alexandre Jaquet" wrote:[color=blue]
> Hi when I read the documentation about how the query it's
> doesn't clear
> my mind about how it can optimize db quering.
>
> When I include a SQL_CACHE clause in a query, does the datas
> are stored
> in memory or not ?
>
> If for exemple I've a series of queries :
>
> SELECT [fields] FROM [db] WHERE [statement= value1]
> SELECT [fields] FROM [db] WHERE [statement= value2]
> SELECT [fields] FROM [db] WHERE [statement= value3]
> SELECT [fields] FROM [db] WHERE [statement= value4]
>
> the only things who change are the statement, any optimisation
> is
> possible by adding "SELECT SQL_CACHE" ?
>
> In the documentation I read :
> " Queries are compared before parsing, so the following two
> queries are
> regarded as different by the query cache:"
>
> In my example does caching by adding SQL_CACHE are done for
> each queries ?
>
> thx for your response[/color]

Besides other responses, you may want to think of this:

Let’s say you have have done a name/address lookup via mysql and it is
cached. Now if the table is updated for whatever reason, the cache is
busted (e.g. a new user added).

In such cases, you may want to look at a non-db related cache. I used
cacheLite. Works pretty nice, for things that I know don’t change
regardless of the tables chaning.

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/mySQL-sql-qu...ict202517.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=741486