Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4681

QUERY CACHE - add normalized query in query cache

    XMLWordPrintable

Details

    • Task
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • None
    • Query Cache
    • 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)

      Attachments

        Activity

          People

            Unassigned Unassigned
            rspadim roberto spadim
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.