MySQL Bugs: #8990: Feature request for new way to clear query cache when table changes

Bug #8990	Feature request for new way to clear query cache when table changes
Submitted:	6 Mar 2005 14:29	Modified:	26 Jun 2005 9:09
Reporter:	Tore Krudtaa	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S4 (Feature request)
Version:		OS:	Any (ALL)
Assigned to:		CPU Architecture:	Any

Description:
This is a feature request for a new way to clear query cache after a table changes.

Your query cache rocks, but there is one thing I would like you to implement.
A new rule to how to clear the query cache when a table is changed.
Instead of clearing all cached queries for the table(s) involved it should only clear the cache for queries where the primary key or a part of the primary key was involved in the change of the table.

Example:

Let say you have the following table.

items

with these fields

MemID
IteID
IteName

Primary key: MemID + IteID

Let say one client make a search where Primary key, or n first fields of Primary key is used.
In this case the Primary key part MemID is used to find all items for a specific member.

Now the query cache contains data from this query.

Then another client make some changes to the items table, but for another MemID used in the query above.

Today, as I know it, the system clears all cached queries where items table is involved.

It would be nice to be able to tell the query cache to only clear cached queries that involves the MemID that was a part of the change in the items table.

This way we keep maximum speed for queries allready in the cache for all those members that was not involved in the table change. And the system also only had to clear cache for those queries where MemID involved in table change was involved.

I do not know how to implement, but could maybe be done by developer setting some parameter in query... i.e. "clear chache using (MemID)" ... or on table level on creation or alter table or both....

How to repeat:
Feature request. See description.

Suggested fix:
Feature request. See description.

A way to implement that:

querys:
select xxx1 where cond1
select xxx2 where cond2
...
select xxxn where condn

update:
update xxx where cond

this should delete from cache all those queries having condx and cond not separate sets.
(example: cond3 - "color = black", cond - "color = red" are separate sets, so the
updated row won't change query result). Some work should be done to handle logic operators.

also queries don't change if atributes(xxx_z) union atributes (cond_z) and atributes(xxx)  are separate.

and some more heuristics can be found, so it can be a viable solution.

this should improve the average web server performance

As I said in my initial Feature Request, what would be the most important thing for me, is to make this work against a part of the primary index, see my example.
I do not care about other fields used in the where statement (other may do so).

If I can control which parts of primary index for a given table that triggers which part of the query cache for a given table to empty I would very happy, and so would my customers.

Regarding my initial example:
An items table:

MemID (member id or customer id or similar)
IteID (unique record for a given member)
CatID
IteName
ItePrice
IteQty

Where primary index are of these fields:
MemID
IteID

select sql_cache IteName 
from items
where MemID=3 and CatID=40

Today this works like a charm exept that if an update is done
using another MemID than the cached query above, my query will still be cleared from memory.

For me it would be really practical to be able to do something like this:

select sql_cache clear_cache using (MemID) IteName 
from items
where MemID=3 and CatID=40

where the part "clear_cache (MemID)" is not valid today.

Or same thing could eventually be done at table creation where table creator instruct MySql how to treat clearing of cache 

The important thing for me is to be able to tell which part of primary index, whole primary index or eventually some other index to be used as basis for telling MySql which records to clear from cache for a given table.

However if it possible to do it in a manner that solve my problem and also optionally take into consideration other fields in the where part, then that would be awsome.......

To the MySql team..... Keep up the good work!