Details
-
Task
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
None
-
None
Description
Add a new query cache option to cache normalized version of a query
SET query_cache_normalized_queries=ON / OFF
|
When query is normalized (optimized) by opt_range (for example) and others query rewriters (partition prune?) and select optimizations, save the normalized (optimized) version of query in query cache (maybe create a new query cache block and link it to same result and table block? or link to 'original' query cache block?)
example:
1)
SELECT * FROM a WHERE 0=0
|
is rewrite to
SELECT `database`.`a`.* FROM `database`.`a`
|
2)
SELECT 1 from a
|
is rewrite to
SELECT 1 FROM `database`.`a`
|
today the 'non optimized' query is cached in query cache, but the second (normalized) isn't, we should add the second one and retry query cache after optimization to get query result using query cache instead executing the query (20us vs a query execution of >=1ms)
Query cache takes in my server near to 10us to get a query in a 100mb query cache with 20000 queries (very fast)
With this MDEV we can improve query cache hits (not only but the query 'executor' will work less), since some queries aren't equal but after normalized they are equal
other feature that we could use to get a higher hit rate:
SET query_cache_normalized_order_where=ON / OFF
|
thinking about a normalized query:
select * from a where b=1 and a=2
|
we can rewrite it ordering the [FIELD] [OPERATOR] [ITEM]
using the example this should be ordered to:
select * from a where a=2 and b=1
|
since "a" < "b", it should get first (when ordering), this allow a higher hit rate too (and add cpu to optimizer part too, that's why it's a optimizer switch)