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

CLOSE - Query cache like features

    XMLWordPrintable

Details

    • Task
    • Status: Closed (View Workflow)
    • Trivial
    • Resolution: Duplicate
    • None
    • None

    Description

      Hi guys, very nice job with query_cache_information plugin!!!
      Could we add some things?

      --------
      1) more columns... "table, rows, query_id, insert method, hits"
      a)tables from one query cache row
      maybe something like "database1.table_a, database2.tableb"
      database + "." + table name
      or maybe a separate table just for this? read (2)
      b)show how many rows in query entry
      this help to optimize app thinks like...
      SELECT * FROM table WHERE ....
      SELECT rows FROM query_cache_information WHERE statment_text="SELECT * FROM table WHERE "...
      IF return 0 rows
      SELECT COUNT(*) FROM table WHERE ...
      c)show query entry id like "primary key" of query_cache_information

      with this we can:
      delete from query_cache_information where query_id = "some_query_key"

      d)'insert method' => 'SQL_CACHE' or 'ALL'

      e)hits

      ok here we will lose some memory per entry (ulong hits)
      maybe will lose some time too, since we need to lock and unlock (i'm right?), like:
      lock the entry
      add +1 to hit var
      unlock it
      it's a good information to understand what query in a table have more or less 'hits'
      maybe a switch or variable could change this feature on/off to avoid time lost
      SET query_cache_entry_hit=ON or OFF

      --------
      2) get queries that have table "x", like
      SELECT * FROM query_cache_information WHERE table like '%,x,%' or table like '%,x' or table like 'x,%' or table='x'

      the problem is the table string, that can be 'x' or 'x, y' or 'y, x' or 'y, x, z' ...

      i don't know if a table string is the better option, or maybe a subquery could do the job...
      in this case we should have a table like:
      database,table,query_id

      and execute:
      SELECT * FROM query_cache_information WHERE query_id IN (
      SELECT query_id FROM query_cache_tables_query_information (or another table name)
      WHERE database='y' AND table='x'
      )

      since query cache must remove a query that have some table when we do insert / update / delete
      i think the new table is a better option than a text column for table name

      --------
      3) show tables that have query cache, like
      SELECT * FROM query_cache_tables_information
      should return: database,table,query_count

      --------
      4) remove a table from query cache
      like when "INSERT INTO table", and query cache remove entries with that table

      maybe via two options...
      DELETE FROM query_cache_information WHERE table = xxx
      or
      DELETE FROM query_cache_table_information WHERE table = xxx

      -------
      that's all =)

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              rspadim roberto spadim
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.