Description:
The query cache is not used when calling a SELECT statement inside an IF statement inside a stored procedure. The query is inserted in the query cache but not used in any subsequent calls of the stored procedure. Inserting a SET statement before the SELECT statement causes the query to be read from the query cache correctly. The query cache is also ignored if the SELECT statement follows an END IF; statement. The SET statement cannot set a variable to itself(i.e. SET iDummy = iDummy has no affect).
How to repeat:
mysql> CREATE TABLE test (test int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (test)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO test VALUES (1);
/******************************************************************/
/* Create a stored proc that has a SELECT statement inside an IF. */
/******************************************************************/
mysql> delimiter //
mysql> CREATE PROCEDURE test_query_cache()
-> BEGIN
-> DECLARE iDummy INTEGER DEFAULT 1;
-> IF iDummy = 1 THEN
-> SELECT * FROM test;
-> END IF;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> show variables like 'que%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 4000000 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+----------+
7 rows in set (0.01 sec)
mysql> reset query cache;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like 'qc%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33545592 |
| Qcache_hits | 33 |
| Qcache_inserts | 879 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 12827 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+
8 rows in set (0.01 sec)
mysql> call test_query_cache;
+------+
| test |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
/******************************************************************/
/* After the first call to the stored proc the number of inserts */
/* has been incremented. */
/******************************************************************/
mysql> show status like 'qc%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33544056 |
| Qcache_hits | 33 |
| Qcache_inserts | 880 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 12827 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.09 sec)
mysql> call test_query_cache;
+------+
| test |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
/******************************************************************/
/* After the second call to the stored proc the number hits and */
/* inserts stay the same. */
/******************************************************************/
mysql> show status like 'qc%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33544056 |
| Qcache_hits | 33 |
| Qcache_inserts | 880 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 12828 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)
mysql> drop procedure test_query_cache;
Query OK, 0 rows affected (0.00 sec)
/******************************************************************/
/* Add a SET statement before the SELECT statement in the stored */
/* proc. */
/******************************************************************/
mysql> delimiter //
mysql> CREATE PROCEDURE test_query_cache()
-> BEGIN
-> DECLARE iDummy INTEGER DEFAULT 1;
-> IF iDummy = 1 THEN
-> SET iDummy = 1;
-> SELECT * FROM test;
-> END IF;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> reset query cache;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like 'qc%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33545592 |
| Qcache_hits | 33 |
| Qcache_inserts | 880 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 12828 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+
8 rows in set (0.01 sec)
mysql> call test_query_cache;
+------+
| test |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
/******************************************************************/
/* After the first call to the stored proc the number of inserts */
/* has been incremented again. */
/******************************************************************/
mysql> show status like 'qc%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33544056 |
| Qcache_hits | 33 |
| Qcache_inserts | 881 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 12828 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)
mysql> call test_query_cache;
+------+
| test |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
/******************************************************************/
/* After the second call to the stored proc the number of hits has*/
/* been incremented. */
/******************************************************************/
mysql> show status like 'qc%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33544056 |
| Qcache_hits | 34 |
| Qcache_inserts | 881 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 12828 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.01 sec)
Suggested fix:
Make sure the query cache is used for all SELECT statements that are found in a stored procedure.
Description: The query cache is not used when calling a SELECT statement inside an IF statement inside a stored procedure. The query is inserted in the query cache but not used in any subsequent calls of the stored procedure. Inserting a SET statement before the SELECT statement causes the query to be read from the query cache correctly. The query cache is also ignored if the SELECT statement follows an END IF; statement. The SET statement cannot set a variable to itself(i.e. SET iDummy = iDummy has no affect). How to repeat: mysql> CREATE TABLE test (test int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (test)) ENGINE=MyISAM; Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO test VALUES (1); /******************************************************************/ /* Create a stored proc that has a SELECT statement inside an IF. */ /******************************************************************/ mysql> delimiter // mysql> CREATE PROCEDURE test_query_cache() -> BEGIN -> DECLARE iDummy INTEGER DEFAULT 1; -> IF iDummy = 1 THEN -> SELECT * FROM test; -> END IF; -> END -> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> show variables like 'que%'; +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | query_alloc_block_size | 8192 | | query_cache_limit | 4000000 | | query_cache_min_res_unit | 4096 | | query_cache_size | 33554432 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | +------------------------------+----------+ 7 rows in set (0.01 sec) mysql> reset query cache; Query OK, 0 rows affected (0.00 sec) mysql> show status like 'qc%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 33545592 | | Qcache_hits | 33 | | Qcache_inserts | 879 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 12827 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+----------+ 8 rows in set (0.01 sec) mysql> call test_query_cache; +------+ | test | +------+ | 1 | +------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) /******************************************************************/ /* After the first call to the stored proc the number of inserts */ /* has been incremented. */ /******************************************************************/ mysql> show status like 'qc%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 33544056 | | Qcache_hits | 33 | | Qcache_inserts | 880 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 12827 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+----------+ 8 rows in set (0.09 sec) mysql> call test_query_cache; +------+ | test | +------+ | 1 | +------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) /******************************************************************/ /* After the second call to the stored proc the number hits and */ /* inserts stay the same. */ /******************************************************************/ mysql> show status like 'qc%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 33544056 | | Qcache_hits | 33 | | Qcache_inserts | 880 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 12828 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+----------+ 8 rows in set (0.00 sec) mysql> drop procedure test_query_cache; Query OK, 0 rows affected (0.00 sec) /******************************************************************/ /* Add a SET statement before the SELECT statement in the stored */ /* proc. */ /******************************************************************/ mysql> delimiter // mysql> CREATE PROCEDURE test_query_cache() -> BEGIN -> DECLARE iDummy INTEGER DEFAULT 1; -> IF iDummy = 1 THEN -> SET iDummy = 1; -> SELECT * FROM test; -> END IF; -> END -> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> reset query cache; Query OK, 0 rows affected (0.00 sec) mysql> show status like 'qc%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 33545592 | | Qcache_hits | 33 | | Qcache_inserts | 880 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 12828 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+----------+ 8 rows in set (0.01 sec) mysql> call test_query_cache; +------+ | test | +------+ | 1 | +------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) /******************************************************************/ /* After the first call to the stored proc the number of inserts */ /* has been incremented again. */ /******************************************************************/ mysql> show status like 'qc%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 33544056 | | Qcache_hits | 33 | | Qcache_inserts | 881 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 12828 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+----------+ 8 rows in set (0.00 sec) mysql> call test_query_cache; +------+ | test | +------+ | 1 | +------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) /******************************************************************/ /* After the second call to the stored proc the number of hits has*/ /* been incremented. */ /******************************************************************/ mysql> show status like 'qc%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 33544056 | | Qcache_hits | 34 | | Qcache_inserts | 881 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 12828 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+----------+ 8 rows in set (0.01 sec) Suggested fix: Make sure the query cache is used for all SELECT statements that are found in a stored procedure.