Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Could you add some details? Like the query, the way you get the zero value, some memory-related DB settings and whatever. –  dezso Sep 21 '13 at 18:01
    
my query is 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 | +-------------------------+---------+ –  user28536 Sep 23 '13 at 4:32
    
And what is the structure of drugs_info and how many row are in it? –  dezso Sep 23 '13 at 5:17
    
sorry for delay and my table structure is drugs_info | 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 no of records are 64500 –  user28536 Sep 24 '13 at 8:36
    
@user28536 Could you paste the output of show variables like 'query_cache%'; ? –  Cristian Porta Sep 25 '13 at 12:34

1 Answer 1

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.