[MDEV-4681] QUERY CACHE - add normalized query in query cache Created: 2013-06-19  Updated: 2014-09-22

Status: Open
Project: MariaDB Server
Component/s: Query Cache
Fix Version/s: None

Type: Task Priority: Minor
Reporter: roberto spadim Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: 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)


Generated at Thu Feb 08 06:58:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.