MySQL Bugs: #40820: Query string "normalization" for query cache.

Bug #40820	Query string "normalization" for query cache.
Submitted:	18 Nov 2008 12:44	Modified:	7 Jul 2017 9:44
Reporter:	Vlad GURDIGA	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S5 (Performance)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Wouldn't it be a plus to have MySQL "normalize" the query string before storing/retrieving it in/to the query cache? I mean these two queries:

SELECT * FROM tbl_name
Select * from tbl_name

would be considered the same, because, in essence they ARE the same. This way if the same query comes out from different parts of application or from different applications, and so, could potentially be formatted differently, would get the same cached response.

By "normalizing" I mean an transformation of the query like this. When let's say I create a table no matter how I choose case of leeters in my SQL statement, the "SHOW CREATE TABLE" always shows a nice, "normalized" form fo the query:

mysql> create table a(i int);
Query OK, 0 rows affected (0.10 sec)

mysql> show create table a\G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.05 sec)

mysql> 

How to repeat:
This is by design and is documented at http://dev.mysql.com/doc/refman/5.0/en/query-cache-how.html.

Suggested fix:
"Normalize" the query string before storing/retrieving it in/to the query cache.

Thank you for a reasonable feature request.

totally agree with this request - i think it's faster to run tolower() for everything but '' contents than to generate another query plan for obviously same query.
also reduces query cache usage.

MySQL will no longer invest in the query cache, see:

http://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/