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
Triage: D5 (Feature request)

[26 Jun 2003 13:41] Daniel Penning
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:
-
[27 Jan 2007 13:33] Konstantin Osipov
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
-------------------------------------------------------
[30 Jan 2007 20:50] Bugs System
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.
[30 Jan 2007 20:51] Guilhem Bichot
the patch linked here is just a preliminary class renaming, no fix in it.
[2 Feb 2007 17:41] Bugs System
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.
[5 Mar 2007 17:07] Konstantin Osipov
Approved by email with a few comments.
[9 Mar 2007 17:45] Bugs System
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.
[15 Mar 2007 12:59] Guilhem Bichot
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
[22 Mar 2007 20:54] Konstantin Osipov
Fixed in 5.1.17
[4 Apr 2007 15:13] Paul Dubois
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.
[11 Apr 2007 21:20] Trudy Pelzer
For additional information, see this section in the MySQL Reference Manual:
http://dev.mysql.com/doc/refman/5.1/en/query-cache-how.html
[4 May 2007 19:33] Bugs System
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".
[24 May 2007 20:15] Bugs System
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
[25 May 2007 14:15] Matthias Leich
Extended QA tests pushed to tree
   mysql-5.1-runtime
(SELECT VERSION() reports 5.1.19-beta)

Removing Additional QA test needed check mark.
[1 Jun 2007 19:23] Bugs System
Pushed into 5.1.20-beta
[23 Jun 2007 17:20] Guilhem Bichot
see BUG#29318