mysql query cache

1. works with Transactions

http://www.mysqlperformanceblog.com/2008/01/29/how-mysql-query-cache-works-with-transactions/ 写道

The result set can be retrieved from query cache (for statements both inside and outside of transactions) until there is a statement inside transactions which modifies the table. As soon as table is modified in transaction it becomes uncachable by query cache until that transaction is committed. Not only query cache can’t be used inside the same transaction which modified data but also in other concurrent transactions which do not even see the changes done yet.

With current approach Innodb can probably do something as simple as marking table “uncachable ” if it has any uncommitted changed which would take care about all complicated aspects of change visibility in different transaction modes.

 

transaction 1 :
start transaction;
select count(*) from parent;  //  result: 1   QCache_hits : 0
select count(*) from parent;  //  result: 1   QCache_hits : 1

transaction 2 :
start transaction;
select count(*) from parent;  //  result: 1   QCache_hits : 2
insert into parent(name) values('new');
select count(*) from parent;  //  result: 2   QCache_hits : 2

transaction 1 :
select count(*) from parent;  //  result: 1   QCache_hits : 2




transaction 2 :
commit;

transaction 1 :
select count(*) from parent;  //  result: 1   QCache_hits : 2

commit;
select count(*) from parent;  //  result: 2   QCache_hits : 2



select count(*) from parent;  //  result: 2   QCache_hits : 3

transaction 2 :
select count(*) from parent;  //  result: 2   QCache_hits : 4

 

 the experiment result perfectly explains  the mechanism of mysql cache query with transactions.

 

 

2. WhiteSpace and comments

http://www.mysqlperformanceblog.com/2008/03/20/mysql-query-cache-whitespace-and-comments/ 写道

You can have whitespace in the start and you can have leading comment and it all works. However the comment which is inside the query works interesting way - the queries with different comments are both treated as same query if only comment is different. However if you change whitespace a bit (see the last query has space after comment deleted) it causes query cache miss.

Whitespace at the start of query does not block query from being cached . Moreover query with 2 spaces in front is considered same as query with 3 spaces in front

Comment at the start of the query does not block query from being cached. However queries with different comments are considered different queries (it is not stripped before hashing) - so you should not put things like current time in such a comment.

Comments inside the query also matter. Meaning if you place comments inside the query or in the end. Though this was always the case

 

select count(*) from parent;  //  QCache_hits : 0
select count(*) from parent;  //  QCache_hits : 1
/*comment*/ select count(*) from parent;  //  QCache_hits : 2             //  whitespace and comment at the start of query 
/*newcomment*/ select count(*) from parent;  //  QCache_hits : 3      //   does not block query from
/*comment*/select count(*) from parent;  //  QCache_hits : 4              //   being cached.

select /*comment*/ count(*) from parent;  //  QCache_hits : 4             // comments inside
select /*new comment*/ count(*) from parent;  //  QCache_hits : 5

select /*comment*/count(*) from parent;  //  QCache_hits : 5         // no whitespace between count(*) and comments.
select /*newcomment*/count(*) from parent;  //  QCache_hits : 6

select /*comment*/   count(*) from parent;  //  QCache_hits : 6      // one more whitespace before count(*)