How does the query cache work? - MySQL

[griemer]

Hi all,

I'm experiencing a strange problem ( IMHO).


query:
SELECT SQL_CALC_FOUND_ROWS `a`.`id` AS `aanvraag_id`, `a`.`tijdstip`,
`a`.`uid`, `a`.`partij`, `a`.`cc`, `a`.`bouwjaar`, `a`.`maand`,
`a`.`provincie_id`, `a`.`woonplaats`, `a`.`klant_bericht` AS `bericht`,
`a`.`merk` AS `merknaam`, LOWER(`a`.`model`) AS `model`, `a`.`ccm`,
LOWER(`a`.`type`) AS `type`, `a`.`carrossid`, `a`.`deurid` AS `deuren`,
`a`.`brandstof`, `a`.`bakid`, `a`.`chassisnr`, `a`.`kenteken` FROM
`aanvraag` AS `a` WHERE `a`.`srt` = 'pw' AND `a`.`merk_id` IN
('1','2','3','4','5','6','7','8','9','10','11','12 ','13','14','15','16','17','18','19','20','21','22 ','23','24','25','26','27','28','29','30','31','34 ','36','37','39','41','42','43','44','45','46','47 ','48','51','53','54','55','57','59','60','61','62 ','63','65','67','69','71','72','73','74','75','76 ','78','79','81','82','84','85','86','87','88','89 ','90','91','92','93','94','95','96','97','98','99 ','101','102','103','106','107','108','109','111', '112','114','115','116','119','120','121','122','1 23','124','125','126','127','128','129','131','134 ','135','136','138','139','140','141','142','146')
AND `a`.`partij` IN ('oz','gp') AND `a`.`id` NOT IN ( SELECT
`aanvraag_id` FROM `abonnee_aanvraag_wis` WHERE `abonnee_id` ='9990')
ORDER BY `a`.`id` DESC LIMIT 0,50

Generates al clickable list with values. When i execute this query for
the first time, execution time is about 2 sec. After refreshing the
page, exec-time is only 0,02 sec. Great, that's what caching does.

Then, when i click in the list, i jump to page2 where another simple
query is executed.
When i push the 'back-button' , i jump back to page1 where the exact
above query is executed, so i would expect an executiion time op 0,02
sec. But is actually is about 0,3 secs. Strange??


**************************************************
SHOW STATUS LIKE '%qcache%'
**************************************************
Variable_name Value
Qcache_free_blocks 525
Qcache_free_memory 29965384
Qcache_hits 17843160
Qcache_inserts 885381
Qcache_lowmem_prunes 0
Qcache_not_cached 33572
Qcache_queries_in_cache 2799
Qcache_total_blocks 6234
**************************************************

Any ideas??
Regards, griemer

[robocomp]

Cached query can become invalid if one of the tables used in the from
clause is being modified. (IIRC, of course)

Besides, you should test you cache and SQL in you database sessions.
Anything can happend while you move from page to page.

(some unnoticable application activitiy, for instance)

[Axel Schwenke]

"griemer" <erfchello.nl> wrote:

>
> I'm experiencing a strange problem ( IMHO).

I would not call this a "problem".

> When i execute this query for
> the first time, execution time is about 2 sec. After refreshing the
> page, exec-time is only 0,02 sec. Great, that's what caching does.
>
> Then, when i click in the list, i jump to page2 where another simple
> query is executed.
> When i push the 'back-button' , i jump back to page1 where the exact
> above query is executed, so i would expect an executiion time op 0,02
> sec. But is actually is about 0,3 secs. Strange??

You've already been told, that entries in the query cache are
invalidated when the underlying tables are modified. That could explain
why your query was slower at the third time.

But there are more caches than the query cache. MyISAM tables cache
indexes in the key_buffer. InnoDB caches whole pages from tablespace
in the innodb_buffer_pool. Your operating system caches blocks read
from mass storage devices. So in most cases the first run of a certain
query is slower than the next ones.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
MySQL User Forums: [url]http://forums.mysql.com/[/url]