MySQL Bugs: #735: Prepared Statements: there is no support for Query Cache

Bug #735	Prepared Statements: there is no support for Query Cache
Submitted:	26 Jun 2003 13:41	Modified:	25 May 2007 14:15
Reporter:	Daniel Penning	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
If exactly the same prepared statement is sent multiple times (e.g. on different connections) caching the result will result in slightly better performance.

How to repeat:
-

A test case for mysqltest language:
--cut: query_cache_ps.test

flush status;
set global query_cache_size=800000;
create table t1 (a int);
insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7);
--disable_result_log
select * from t1;
--enable_result_log
show status like 'Qcache_hits';
show status like 'Qcache_queries_in_cache';
--disable_result_log
select * from t1;
--enable_result_log
show status like 'Qcache_hits';
show status like 'Qcache_queries_in_cache';

--end cut

To reproduce, run: ./mtr --ps-protocol query_cache_ps

Results with and without ps protocol:
-------------------------------------------------------
*** r/query_cache_ps.result	2007-01-27 16:28:26.000000000 +0300
--- r/query_cache_ps.reject	2007-01-27 16:29:53.000000000 +0300
***************
*** 0 ****
--- 1,18 ----
+ flush status;
+ set global query_cache_size=800000;
+ create table t1 (a int);
+ insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7);
+ select * from t1;
+ show status like 'Qcache_hits';
+ Variable_name	Value
+ Qcache_hits	0
+ show status like 'Qcache_queries_in_cache';
+ Variable_name	Value
+ Qcache_queries_in_cache	0
+ select * from t1;
+ show status like 'Qcache_hits';
+ Variable_name	Value
+ Qcache_hits	0
+ show status like 'Qcache_queries_in_cache';
+ Variable_name	Value
+ Qcache_queries_in_cache	0
-------------------------------------------------------
-------------------------------------------------------
*** r/query_cache_ps.result	2007-01-27 16:28:26.000000000 +0300
--- r/query_cache_ps.reject	2007-01-27 16:29:37.000000000 +0300
***************
*** 0 ****
--- 1,18 ----
+ flush status;
+ set global query_cache_size=800000;
+ create table t1 (a int);
+ insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7);
+ select * from t1;
+ show status like 'Qcache_hits';
+ Variable_name	Value
+ Qcache_hits	0
+ show status like 'Qcache_queries_in_cache';
+ Variable_name	Value
+ Qcache_queries_in_cache	1
+ select * from t1;
+ show status like 'Qcache_hits';
+ Variable_name	Value
+ Qcache_hits	1
+ show status like 'Qcache_queries_in_cache';
+ Variable_name	Value
+ Qcache_queries_in_cache	1
-------------------------------------------------------

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19039

ChangeSet@1.2413, 2007-01-30 22:48:05+01:00, guilhem@gbichot3.local +7 -0
  Mechanical class renaming:
  Protocol_simple->Protocol_text; Protocol_prep->Protocol_binary
  and also THD::protocol_simple->THD::protocol_text,
  THD::protocol_prep->THD::protocol_binary.
  Reason: the binary protocol is not bound to be used only with
  prepared statements long term (see WL#3559 "Decouple binary protocol
  from prepared statements"). Renaming now is pressing because
  the fix for BUG#735 "Prepared Statements: there is
  no support for Query Cache" will introduce a new member
  in class Query_cache_flags telling about the protocol's nature.
  Other reason: "simple" is less accurate than "text".
  Future patches for BUG#735 will rely on this cset.

the patch linked here is just a preliminary class renaming, no fix in it.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19261

ChangeSet@1.2414, 2007-02-02 19:38:24+01:00, guilhem@gbichot3.local +13 -0
  Fix for BUG#735 "Prepared Statements: there is no support for Query
  Cache".
  WL#1569 "Prepared Statements: implement support of Query Cache".
  Prepared SELECTs did not look up in the query cache, and their results
  were not stored in the query cache. This made them slower than
  non-prepared SELECTs in some cases.
  The fix is to re-use the expanded query (the prepared query where
  "?" placeholders are replaced by their values, at execution time)
  for searching/storing in the query cache.
  It works fine for statements prepared via mysql_stmt_prepare(), which
  are the most commonly used and were the scope of this bugfix and WL.
  It works less fine for statements prepared via the SQL command
  PREPARE...FROM, which are still not using the query cache if they
  have at least one parameter (because then the expanded query contains
  names of user variables, and user variables don't work with the
  query cache, even in non-prepared queries).
  Note that results from prepared SELECTs, which are in the binary
  protocol, and results from normal SELECTs, which are in the text
  protocol, ignore each other in the query cache, because a result in the
  binary protocol should never be served to a SELECT expecting the text
  protocol and vice-versa.
  One question for reviewers is marked with QQ in sql_prepare.cc.
  Trudy: this is a commit in mysql-5.1, but I will push it only in trees
  allowed by the authorities.
  Note, after this patch, bug 25843 starts applying to query cache
  ("changing default database between PREPARE and EXECUTE of statement
  breaks binlog"), we need to fix it.

Approved by email with a few comments.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21628

ChangeSet@1.2463, 2007-03-09 18:09:57+01:00, guilhem@gbichot3.local +13 -0
  Fix for BUG#735 "Prepared Statements: there is no support for Query
  Cache".
  WL#1569 "Prepared Statements: implement support of Query Cache".
  Prepared SELECTs did not look up in the query cache, and their results
  were not stored in the query cache. This made them slower than
  non-prepared SELECTs in some cases.
  The fix is to re-use the expanded query (the prepared query where
  "?" placeholders are replaced by their values, at execution time)
  for searching/storing in the query cache.
  It works fine for statements prepared via mysql_stmt_prepare(), which
  are the most commonly used and were the scope of this bugfix and WL.
  It works less fine for statements prepared via the SQL command
  PREPARE...FROM, which are still not using the query cache if they
  have at least one parameter (because then the expanded query contains
  names of user variables, and user variables don't work with the
  query cache, even in non-prepared queries).
  Note that results from prepared SELECTs, which are in the binary
  protocol, and results from normal SELECTs, which are in the text
  protocol, ignore each other in the query cache, because a result in the
  binary protocol should never be served to a SELECT expecting the text
  protocol and vice-versa.
  Note, after this patch, bug 25843 starts applying to query cache
  ("changing default database between PREPARE and EXECUTE of statement
  breaks binlog"), we need to fix it.

Queued to 5.1-runtime.
For the doc:
Intro: we have two kinds of prepared statements, as said in 
http://dev.mysql.com/doc/refman/5.1/en/sqlps.html
Those manipulated via these API calls:
http://dev.mysql.com/doc/refman/5.1/en/c-api-prepared-statements.html
which I call "C API PS" below,
and those manipulated via PREPARE FROM detailed at
http://dev.mysql.com/doc/refman/5.1/en/sqlps.html,
which I call "SQL PS below".
Now the fix:
  Prepared SELECTs did not look up in the query cache, and their results
  were not stored in the query cache. This made them slower than
  non-prepared SELECTs in some cases.
  The fix is to re-use the expanded query (the prepared query where
  "?" placeholders are replaced by their values, at execution time)
  for searching/storing in the query cache.
  It works fine for C API PS, which
  are the most commonly used and were the scope of this bugfix.
  It works less fine for SQL PS, which are still not using the query cache if they
  have at least one parameter (because then the expanded query contains
  names of user variables, and user variables don't work with the
  query cache, even in non-prepared queries).
  Note that results from prepared SELECTs, which are in the binary
  protocol, and results from normal SELECTs, which are in the text
  protocol, ignore each other in the query cache, because a result in the
  binary protocol should never be served to a SELECT expecting the text
  protocol and vice-versa.
  Note, after this patch, BUG#25843 starts applying to query cache
  ("changing default database between PREPARE and EXECUTE of statement
  breaks binlog"), I have mentioned this in BUG#25843.
I have found these two pages saying that prepared statements don't work with the query cache, which should be updated:
http://dev.mysql.com/doc/refman/5.1/en/c-api-prepared-statement-problems.html
http://dev.mysql.com/doc/refman/5.1/en/query-cache-how.html

Fixed in 5.1.17

Noted in 5.1.17 changelog, and revised the two sections
in the manual mentioned earlier that deal with the query
cache and prepared statements.

Setting report to QA testing.

For additional information, see this section in the MySQL Reference Manual:
http://dev.mysql.com/doc/refman/5.1/en/query-cache-how.html

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26139

ChangeSet@1.2576, 2007-05-05 21:33:11+02:00, mleich@four.local.lan +12 -0
  This changeset belongs to the fix of Bug#735 Prepared Statements: there is no support for Query Cache
  
  The final changeset to be pushed will be most probably slightly different because the modifications
  to mysql-test-run.pl should rather go int MySQL 4.1.
  
  - Create "--ps-protocol" and no "--<whatever>-protocol" variants of the former tests
    t/grant_cache.test and t/query_cache_sql_prepare.test.
  - Introduce environment variables for the protocol related mysqltest startup options.
  - There are no additional
              include/have_<whatever>_protocol.inc
              include/have_no_<whatever>_protocol.inc
    files etc., because IMHO there is a low probability that we need them frequent enough.
    The decision to run or skip a test could be easy coded into the toplevel scripts stored
    within the directory "t".

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27311

ChangeSet@1.2514, 2007-05-24 22:13:49+02:00, mleich@four.local.lan +11 -0
  This changeset belongs to the fix of Bug#735 Prepared Statements: there is no support for Query Cache
    
  - Create "--ps-protocol" and no "--<whatever>-protocol" variants of the former tests
    t/grant_cache.test and t/query_cache_sql_prepare.test.
  - Some additional subtest and fixes of bugs
  - Minor improvements

Extended QA tests pushed to tree
   mysql-5.1-runtime
(SELECT VERSION() reports 5.1.19-beta)

Removing Additional QA test needed check mark.

Pushed into 5.1.20-beta

see BUG#29318