maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

Hi again kentoku!
i'm using spider :) thanks for the last feature

now i have another idea about query cache =)

i'm starting test of VP (vertical partitioning)
for example i have 3 tables:
1) vp table
2) data1 table (many select)
3) data2 table (many update)

both table will not be used outside (1), for example, i'm considering that
we will only have select/update/delete from table (1) and never have an
update data1, or update data2, in this case user must invalidate query cache

what will happen? i will give examples....

1) insert/delete
insert into vp values ( ... )
delete from vp where (...)

for now this always invalidate query cache
maybe in future only partition that receive this row, for example if we
have an horizontal partition and only one partition changed

2) select
2.1)select (fields from data1) from vp where (no fields from data2)
this kind of query will only use data1 table, in this case it's a table
that never change (it have low writes and many reads)
we can query cache it, but instead of leaving query cache table name as
"vp", we mark it as "vp-data1"

2.2) select (fields from data1 and data2) from vp where (fields from data1
and data2)
this kind of query use both tables, here the second table have many updates
if we include this query to query cache we will have a query cache
invalidation very soon, this isn't a nice query to cache, but if we cache
it we could call table name as "vp" or "vp-data1 + vp-data2"

3) update
here is the magic...
3.1) update vp set fields_from_data2=... where
fields_fromdata_1_or_data2=...
here we use fields from data1 and data2 to "find" rows, but we only modify
fields from data2 (the write intensive table)
in this case we will only invalidate queries from table "vp-data2" and not
"vp-data1", in other words, all queries that use (2.1) will not be
invalidate, a better query cache hit rate =)

---
observations
since vp-data2 change many many times, could be nice to block query cache
of selects that use this table
i'm considering it as a parameter of vp table, something like
comment 'query cache data1'
in this case only data1 will be query cache, if we receive a query that use
data2 we will not cache it

sounds crazy? :)
ok i know that query cache have some problems with mutex, but it's another
history we could have a better hit rate with this feature (at least when
correctly used)
check that query cache become inconsistent if we change data1 or data2
outside vp table

well i think that's all :)
thanks!