[MDEV-4671] QUERY CACHE - black list Created: 2013-06-17  Updated: 2015-11-17

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Minor
Reporter: roberto spadim Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-6358 QUERY CACHE - Ignore Table List Open

 Description   

We could implement a black list to query cache

SELECT * FROM schema.table

an idea is a "mysql.query_cache_deny_tables" table with four columns:

SCHEMA varchar(64), 
TYPE enum ('=','LIKE')
TABLE  varchar(255)
PARTITION varchar(255)
PRIMARY KEY (SCHEMA,TYPE,TABLE,PARTITION) BTREE (usefull with LIKE)

we could check if table being cache is inside this table with 2 queries:
1) type="="

SELECT 1
FROM mysql_query_cache_deny_tables 
WHERE 
SCHEMA="#table_schema#" AND 
TYPE="=" AND 
TABLE_LIKE IN ("#table_name#","") AND 
PARTITION IN ("#partition#",'')
LIMIT 1

2) type="LIKE"

SELECT 1 
FROM mysql_query_cache_deny_tables 
WHERE 
SCHEMA="#table_schema#" AND 
TYPE="LIKE" AND 
"#table_name#" LIKE TABLE AND 
"#partition_name#" LIKE PARTITION
LIMIT 1

if this query return any rows we discart the query cache
table ="" => don't cache any table from this SCHEMA,
partition ="" => table without partitions or all partitions of this table

obvious more rows inside this table = slower query cache, must check what's fast, a "table "LIKE field, or a hash table with many "=" conditions and no "like" condition

maybe we could add two others types: "!=" and "NOT LIKE"


Generated at Thu Feb 08 06:58:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.