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

SELECT - Query cache only result

    XMLWordPrintable

Details

    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

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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