MySQL Bugs: #64361: SHOW TABLES FROM ... increments Select

Bug #64361	SHOW TABLES FROM ... increments Select_scan Status
Submitted:	17 Feb 2012 11:12	Modified:	17 Feb 2012 14:36
Reporter:	Henrik Brinkmann	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.5.18-log, 5.5.20	OS:	Linux (x86_64 debian5.0 )
Assigned to:		CPU Architecture:	Any
Tags:	Select_scan, show tables

Description:
This might not be a bug, but it makes it difficult to analyze and optimize queries.
Every Execution of "SHOW TABLES LIKE 'MyTableName'" increments the "Select_scan" STATUS by 1.
So with this the interpretation of the "Select_scan" status becomes difficult when such queries are used often (in my case to check whether a table already exists).

Using the alternative selection via INFORMATION_SCHEMA results in incrementation of "Select_scan" by 2:
SELECT COUNT(*) FROM Information_schema.tables WHERE 
table_name = "my_table" 
AND table_schema = "mydb";

How to repeat:
FLUSH STATUS;
0 row(s) affected

SHOW STATUS LIKE "Select_scan";
Variable_name  Value   
-------------  --------
Select_scan    0       

SHOW TABLES LIKE "my_table";
Tables_in_mydb (my_table)  
------------------------------

SHOW STATUS LIKE "Select_scan";
Variable_name  Value   
-------------  --------
Select_scan    1       

Suggested fix:
There should be another way (without incrementing "Select_scan") to check if a table exists or SHOW TABLES itself should not increment "Select_scan"

Yes, this is easy to verify:

mysql> show status like '%scan%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Select_scan   | 5     |
| Sort_scan     | 0     |
+---------------+-------+
2 rows in set (0.01 sec)

mysql> show tables from mydb;
+----------------+
| Tables_in_mydb |
+----------------+
| table1         |
| table2         |
+----------------+
2 rows in set (0.01 sec)

mysql> show status like '%scan%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Select_scan   | 6     |
| Sort_scan     | 0     |
+---------------+-------+
2 rows in set (0.00 sec)

and it clearly contradicts explanation of this status variable from the manual, http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Select_scan.

The reason is obvious - temporary table in memory is created, but still this hardly should be considered a full table scan worth reporting, comparing to explicit select * from big_table.