| Bug #23209 | Functions don't use Query Cache | ||
|---|---|---|---|
| Submitted: | 12 Oct 2006 11:31 | Modified: | 5 Feb 2007 20:40 |
| Reporter: | Richard T. | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S5 (Performance) |
| Version: | 5.0.32-BK, 5.0.26 & 5.1.11-beta | OS: | Linux (SuSE Linux 10.1) |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
| Tags: | query cache | ||
[12 Oct 2006 13:26]
Valeriy Kravchuk
Thank you for a problem report. Please, enable general query log and check, if these SELECTs are really the same in case of function (with parameter!). I believe, the reason is similar to described at http://dev.mysql.com/doc/refman/5.0/en/query-cache-how.html: "A query also is not cached under these conditions: ... - It refers to user variables. ... - It was issued as a prepared statement, even if no placeholders were employed." I belive, is SP select with parameter is equivalent to one of the above...
[13 Oct 2006 8:41]
Richard T.
Hi, I show my way ...
mysql> SHOW VARIABLES LIKE 'query%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| 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.00 sec)
mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33544088 |
| Qcache_hits | 2 | <<---
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 14 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)
mysql> SELECT nachname FROM user WHERE ID = 1;
mysql> SELECT nachname FROM user WHERE ID = 1;
mysql> SELECT nachname FROM user WHERE ID = 1;
mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33544088 |
| Qcache_hits | 5 | <<---
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 15 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)
mysql> SELECT get_UserName(1);
mysql> SELECT get_UserName(1);
mysql> SELECT get_UserName(1);
mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33544088 |
| Qcache_hits | 5 | <<---
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 16 | <<---
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)
And now with an other client
mysql> SELECT get_UserName(1);
mysql> SELECT get_UserName(1);
mysql> SELECT get_UserName(1);
mysql> show status LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33544088 |
| Qcache_hits | 5 | <<---
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 18 | <<---
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)
And this is the mysql-logfile
061013 10:24:28 6 Query SHOW VARIABLES LIKE 'query%'
061013 10:24:57 6 Query SHOW STATUS LIKE 'Qcache%'
061013 10:25:31 6 Query SELECT nachname FROM user WHERE ID = 1
061013 10:25:33 6 Query SELECT nachname FROM user WHERE ID = 1
061013 10:25:34 6 Query SELECT nachname FROM user WHERE ID = 1
061013 10:25:54 6 Query SHOW STATUS LIKE 'Qcache%'
061013 10:26:06 6 Query SELECT get_UserName(1)
061013 10:26:07 6 Query SELECT get_UserName(1)
061013 10:26:08 6 Query SELECT get_UserName(1)
061013 10:26:19 6 Query SHOW STATUS LIKE 'Qcache%'
061013 10:36:49 7 Connect root@localhost on
061013 10:36:59 7 Query SELECT DATABASE()
7 Init DB verwaltung
061013 10:37:22 7 Query SELECT get_UserName(1)
061013 10:37:24 7 Query SELECT get_UserName(1)
061013 10:37:25 7 Query SELECT get_UserName(1)
061013 10:38:06 7 Query show status LIKE 'Qcache%'
[23 Nov 2006 17:19]
Valeriy Kravchuk
Verified with 5.0.32-BK on Linux: mysql> create function ft() returns integer deterministic begin declare ret i nteger; select 1 into ret from t; return ret; end;// Query OK, 0 rows affected (0.04 sec) mysql> show status like 'qcache%'// +-------------------------+--------+ | Variable_name | Value | +-------------------------+--------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 989064 | | Qcache_hits | 1 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 2 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+--------+ 8 rows in set (0.00 sec) mysql> delimiter ; mysql> select ft(); +------+ | ft() | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> show status like 'qcache%'; integer determin +-------------------------+--------+ | Variable_name | Value | +-------------------------+--------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 989064 | | Qcache_hits | 1 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 3 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+--------+ 8 rows in set (0.00 sec) mysql> select ft() from t; +------+ | ft() | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> select ft() from t; +------+ | ft() | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> show status like 'qcache%'; +-------------------------+--------+ | Variable_name | Value | +-------------------------+--------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 989064 | | Qcache_hits | 1 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 4 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+--------+ 8 rows in set (0.00 sec) I am almost sure it is intended behaviour, but in this case it should be explicitely documented at http://dev.mysql.com/doc/refman/5.0/en/query-cache-how.html.
[18 Jan 2007 20:57]
Konstantin Osipov
Hello Stefan, I think this is a documentation bug. Query cache by design caches only network packets. A function can not make use of a cached result set because it's already encoded into network representation (a sequence of packets that will be sent to the client). We will not support this in the foreseeable future.
[5 Feb 2007 20:40]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products. I'll update the referenced section to point out that queries executed within stored routines don't use the query cache.
Description: I use a simply SELECT Query and then the result is cached. E.G. SELECT Name FROM user WHERE ID = 5; If I use this SELECT in a function then the result ist not cached. How to repeat: CREATE FUNCTION `get_UserName`(pi_UserID INTEGER(11)) RETURNS varchar(50) DETERMINISTIC SQL SECURITY INVOKER COMMENT '' BEGIN DECLARE p_sUserName CHAR(50); SELECT nachname INTO p_sUserName FROM user WHERE ID = pi_UserID; return p_sUserName; END; SELECT get_UserName(5);