Hi guys, very nice job with query_cache_information plugin!!!
Could we add some things?
1) more columns... "table, rows, query_id, insert method, hits"
a)tables from one query cache row
maybe something like "database1.table_a, database2.tableb"
database + "." + table name
or maybe a separate table just for this? read (2)
b)show how many rows in query entry
this help to optimize app thinks like...
SELECT * FROM table WHERE ....
SELECT rows FROM query_cache_information WHERE statment_text="SELECT * FROM table WHERE "...
IF return 0 rows
SELECT COUNT(*) FROM table WHERE ...
c)show query entry id like "primary key" of query_cache_information
with this we can:
delete from query_cache_information where query_id = "some_query_key"
d)'insert method' => 'SQL_CACHE' or 'ALL'
ok here we will lose some memory per entry (ulong hits)
maybe will lose some time too, since we need to lock and unlock (i'm right?), like:
lock the entry
add +1 to hit var
it's a good information to understand what query in a table have more or less 'hits'
maybe a switch or variable could change this feature on/off to avoid time lost
SET query_cache_entry_hit=ON or OFF
2) get queries that have table "x", like
SELECT * FROM query_cache_information WHERE table like '%,x,%' or table like '%,x' or table like 'x,%' or table='x'
the problem is the table string, that can be 'x' or 'x, y' or 'y, x' or 'y, x, z' ...
i don't know if a table string is the better option, or maybe a subquery could do the job...
in this case we should have a table like:
SELECT * FROM query_cache_information WHERE query_id IN (
SELECT query_id FROM query_cache_tables_query_information (or another table name)
WHERE database='y' AND table='x'
since query cache must remove a query that have some table when we do insert / update / delete
i think the new table is a better option than a text column for table name
3) show tables that have query cache, like
SELECT * FROM query_cache_tables_information
should return: database,table,query_count
4) remove a table from query cache
like when "INSERT INTO table", and query cache remove entries with that table
maybe via two options...
DELETE FROM query_cache_information WHERE table = xxx
DELETE FROM query_cache_table_information WHERE table = xxx
that's all =)