Bug #22768 Com_select not incremented on Query Cache hits

• Submitted: 28 Sep 2006 3:06
• Modified: 28 Sep 2006 17:48
• Reporter: Dean Ellis
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Assigned to: Paul DuBois
• CPU Architecture: Any

[28 Sep 2006 3:06] Dean Ellis

Description:
Com_select is not incremented for SELECT queries served by the query cache.

How to repeat:
FLUSH STATUS;
CREATE TABLE t1 SELECT 1 a;
SET GLOBAL query_cache_size=32*1024*1024;
SHOW STATUS LIKE 'Com_select';
SHOW STATUS LIKE 'Qcache_hits';
SELECT a FROM t1;
SHOW STATUS LIKE 'Com_select';
SHOW STATUS LIKE 'Qcache_hits';
SELECT a FROM t1;
SELECT a FROM t1;
SELECT a FROM t1;
SHOW STATUS LIKE 'Com_select';
SHOW STATUS LIKE 'Qcache_hits';
DROP TABLE t1;

Suggested fix:
Older scripts may have unfortunately worked around this by evaluating Com_select+qcache_hits to determine how many SELECT statements have been issued, so "fixing" this may break quite a number of things.

It is not, however, clear from the manual that "The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed" excludes SELECTs which are served by the query cache.

So, either note it in the manual, or perhaps try something like:

===== sql_cache.cc 1.97 vs edited =====
--- 1.97/sql/sql_cache.cc       2006-09-27 22:13:37 -05:00
+++ edited/sql_cache.cc 2006-09-27 22:12:19 -05:00
@@ -1249,6 +1249,8 @@
   }
   move_to_query_list_end(query_block);
   hits++;
+  statistic_increment(thd->status_var.com_stat[SQLCOM_SELECT],
+                                         &LOCK_status);
   STRUCT_UNLOCK(&structure_guard_mutex);
 
   /*

[28 Sep 2006 11:49] Valeriy Kravchuk

Thank you for a problem report. Verified just as described with 5.0.26-BK on Linux. I think, it is a documentation request.

[28 Sep 2006 17:48] Paul DuBois

http://dev.mysql.com/doc/refman/5.0/en/query-cache-how.html says:

If a query result is returned from query cache, the server increments
the Qcache_hits status variable, not Com_select. See Section 5.14.4, 
“Query Cache Status and Maintenance”.

But I'll point this out in the Com_xxx paragraph as well.