Mysql – query cache hit value is not changing in the database

innodbMySQLquery-cache

The query cache hit value is not changing in my database only for some queries.If I write 

select * from drugs_info limit 1000;

the query cache hit has changed, but if I execute the following queries for two to three times the query cache hit is not changing. The queries as follows:

select * from drugs_info limit 10000; 

select * from drugs_info limit 15000;

like this. I don't know what is the problem.

The table, which contains 64500 rows:

CREATE TABLE drugs_info ( 
    did int(11) NOT NULL AUTO_INCREMENT, 
    brand_name varchar(150) DEFAULT NULL, 
    generic varchar(500) DEFAULT NULL, 
    tradename varchar(150) DEFAULT NULL, 
    manfactured varchar(100) DEFAULT NULL, 
    unit varchar(300) DEFAULT NULL, 
    type varchar(50) DEFAULT NULL, 
    quantity varchar(50) DEFAULT NULL, 
    price float DEFAULT NULL, 
    PRIMARY KEY (did), 
    KEY id1 (brand_name,generic) 
)
ENGINE=InnoDB AUTO_INCREMENT=64379 DEFAULT CHARSET=latin1;

and the query:

select * from drugs_info;

and my settings are 

mysql> show status like 'qcache%'; 
+-------------------------+---------+ 
| Variable_name           |  Value  | 
+-------------------------+---------+ 
| Qcache_free_blocks      | 1       | 
| Qcache_free_memory      | 8958376 | 
| Qcache_hits             | 0       | 
| Qcache_inserts          | 0       | 
| Qcache_lowmem_prunes    | 0       | 
| Qcache_not_cached       | 2       | 
| Qcache_queries_in_cache | 0       | 
| Qcache_total_blocks     | 1       | 
+-------------------------+---------+

Why is this?

Best Answer

You have two things running into each other

  • Issue #1 : Differing Queries
  • Issue #2 : InnoDB Storage Engine

Issue #1 : Differing Queries

Look at your three queries

select * from drugs_info limit 1000; 
select * from drugs_info limit 10000; 
select * from drugs_info limit 15000;

Each query stores a unique result (if the results are stored). If the results are too big (as in the case of the second and third queries), the result sets must be retrieved again.

You could potentially solve this with a bigger query cache, which must be big enough to hold each query's result set. This leads us into ...

Issue #2 : InnoDB Storage Engine

Unfortunately, the InnoDB Storage Engine likes to play traffic cop with the query cache. I had discussed this before in my answer to Is the overhead of frequent query cache invalidation ever worth it? where I learned this pages 213-215 in the book (Second Edition)

lkxcd

If you really want the query cache, you have to meticulously set the query cache size to suit your needs. In almost all cases, it is best to set query_cache_size to 0 when dealing with InnoDB.

First, add this to my.cnf

[mysqld]
query_cache_size=0

Then, run this

mysql> SET GLOBAL query_cache_size = 0;

and you should be all set.

Give it a Try !!!

Related Question