Professional Web Applications Themes

How does the query cache work? - MySQL

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

  1. #1

    Default How does the query cache work?

    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

    griemer Guest

  2. #2

    Default Re: How does the query cache work?

    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)

    robocomp Guest

  3. #3

    Default Re: How does the query cache work?

    "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]
    Axel Schwenke Guest

Similar Threads

  1. Query use to work on CF 4.5
    By billy_good in forum Coldfusion Database Access
    Replies: 3
    Last Post: February 8th, 06:46 PM
  2. Replies: 0
    Last Post: November 4th, 05:38 PM
  3. Query dosen't work
    By gcmenotti in forum Coldfusion Database Access
    Replies: 1
    Last Post: September 27th, 11:31 PM
  4. Query used to work
    By mikey999 in forum Coldfusion Database Access
    Replies: 5
    Last Post: April 14th, 10:12 PM
  5. SQL/ASP.NET getting DATE to work in query
    By darrel in forum Dreamweaver AppDev
    Replies: 3
    Last Post: March 23rd, 11:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139