Description:
I set the config option "query_cache_size=1355776" and "query_cache_type=1" in the config file in order to use Query_cache. I am amazed that every time I update option "max_sort_length" to a large value, the Query_cache stores the redundant queries, which might reduce query efficiency and waste cache space. Because updating "max_sort_length" had no effect on results. Maybe Query_cacher should have a smart identification for redundant queries. The test case and result are followed.
How to repeat:
Test:
reset query cache;
flush status;
create database if not exists mysqltest;
create table mysqltest.t1 (i int not null);
insert into mysqltest.t1 (i) values (1);
show status like "Qcache_queries_in_cache";
select * from mysqltest.t1;
show status like "Qcache_queries_in_cache";
set session max_sort_length = 51200;
select * from mysqltest.t1;
show status like "Qcache_queries_in_cache";
set session max_sort_length = 51202;
select * from mysqltest.t1;
show status like "Qcache_queries_in_cache";
select * from mysqltest.t1;
show status like "Qcache_queries_in_cache";
drop database mysqltest;
Result:
create database if not exists mysqltest;
create table mysqltest.t1 (i int not null);
insert into mysqltest.t1 (i) values (1);
show status like "Qcache_queries_in_cache";
Variable_name Value
Qcache_queries_in_cache 0
select * from mysqltest.t1;
i
1
show status like "Qcache_queries_in_cache";
Variable_name Value
Qcache_queries_in_cache 1
set session max_sort_length = 51200;
select * from mysqltest.t1;
i
1
show status like "Qcache_queries_in_cache";
Variable_name Value
Qcache_queries_in_cache 2
set session max_sort_length = 51202;
select * from mysqltest.t1;
i
1
show status like "Qcache_queries_in_cache";
Variable_name Value
Qcache_queries_in_cache 3
select * from mysqltest.t1;
i
1
show status like "Qcache_queries_in_cache";
Variable_name Value
Qcache_queries_in_cache 3
drop database mysqltest;
Suggested fix:
Maybe Query_cacher should have a smart identification for redundant queries.
