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

[18 Nov 2008 12:44] Vlad GURDIGA
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.
[18 Nov 2008 13:08] Valeriy Kravchuk
Thank you for a reasonable feature request.
[6 Jun 2009 0:25] lukasz lury
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.
[7 Jul 2017 9:44] Erlend Dahl
MySQL will no longer invest in the query cache, see:

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