Details
-
Task
-
Status: Closed (View Workflow)
-
Trivial
-
Resolution: Duplicate
Description
This MDEV add new flag in queries to return only if the query was found in query cache
example:
QUERY:
SELECT SQL_CACHE * FROM SOME_TABLE WHERE SOME_FIELD='XXX'
|
now we want the result only if it's in cache... this first one will work only if we set strip comments variable "query_cache_strip_comments=on"
/*SQL_CACHE_ONLY*/SELECT SQL_CACHE * FROM SOME_TABLE WHERE SOME_FIELD='XXX'
|
or
SET result_from_query_cache_only=ON / AUTO;
|
SELECT SQL_CACHE * FROM SOME_TABLE WHERE SOME_FIELD='XXX'
|
SET result_from_query_cache_only=OFF; /* if set to AUTO after the query being executed set to OFF again */
|
if query was not found in cache, it will return an error "QUERY NOT FOUND IN QUERY CACHE" like today "table don't exists" or another kind of error
why this?
when a query is cached we can return it very fast (6us some times), since table can change a lot and query cache is pruned when table change (not when result of query cache change), the query cache is lost very often, some apps cache the return in a external (normally memcache) and check if the query result is inside query, this is a problem, since we do:
1) get cache from memcache (10us) -> if we found here we expend 10us
|
2) get cache from database (10us) -> if we found here we expend 20us
|
3) execute query in database (>1000us) -> if we found here we expend 20us + query time
|
instead of:
1) get cache from database (10us) -> if we found here we expend 10us (faster than 20us)
|
2) get cache com memcache (10us) -> if we found here we expend 20us (slower than 10us, but it's a second source of cache)
|
3) execute query in database (>1000us) -> if we found here we expend the same 20us + query time
|
today we expend 10us, but could expend it only in database instead of memcache (we have a connection openned at memcache + a connection openned at mysql/mariadb)
that is not a MUST do task, it's just a feature of how to use query cache and get a better cache hit rate
Attachments
Issue Links
- is part of
-
MDEV-4584 QUERY CACHE - SQL Control Interface
- Open