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

Full table scan in INFORMATION_SCHEMA.PARAMETERS/ROUTINES

Details

    Description

      It seems there is an issue when querying on I_S.PARAMETERS table (used in java metadata) :
      when doing this kind of query (one result) :
      SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA = 'testj' AND SPECIFIC_NAME = 'p2_512'
      It takes 15ms if 400 parameters in I_S.PARAMETERS table
      It takes 65ms if 4K parameters
      It takes 350ms if 20K parameters

      It seems to execute a full table scan in INFORMATION_SCHEMA.PARAMETERS

      The same query takes 6ms on mysql 8 with 20K same parameters

      Attachments

        1. mdev20609.diff
          8 kB
        2. screenshot-1.png
          screenshot-1.png
          5 kB
        3. screenshot-2.png
          screenshot-2.png
          18 kB

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. CONJ-684 Performance of con.getMetaData().getProcedureColumns(null, null, name, "%") is much lower than mysql jdbc

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29550 Valgrind/MSAN uninitialised value errors upon SHOW PROCEDURE STATUS with condition on Db

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29858 Missing DBUG_RETURN or DBUG_VOID_RETURN macro in function "fill_schema_proc"

          • Major - Major loss of function.
          • Closed
          is part of

          Task - A task that needs to be done. MDEV-29547 prepare 10.11.0 preview releases

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-28979 Feature Request: Allow for indexes to be created on the information_schema tables

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-29104 Performance Issues Reading information_schema.PARAMETERS

          • Major - Major loss of function.
          • Closed

          Activity

            Transition Time In Source Status Execution Times
            Julien Fritsch made transition -
            Open Confirmed
            		825d 2h 1m 1
            Oleksandr Byelkin made transition -
            Confirmed In Progress
            		210d 23h 52m 1
            Oleksandr Byelkin made transition -
            In Progress Stalled
            		1h 17m 1
            Oleg Smirnov made transition -
            In Progress In Review
            		1d 7h 40m 1
            Sergei Petrunia made transition -
            In Review Stalled
            		1d 25m 1
            Sergei Petrunia made transition -
            Stalled In Testing
            		8s 1
            Oleg Smirnov made transition -
            Stalled In Progress
            		54d 18h 30m 2
            Oleg Smirnov made transition -
            In Progress In Testing
            		22h 33m 1
            Ramesh Sivaraman made transition -
            In Testing Stalled
            		21d 14h 42m 2
            Oleg Smirnov made transition -
            Stalled Closed
            		5d 23h 36m 1

            People

              oleg.smirnov Oleg Smirnov
              diego dupin Diego Dupin
              Votes:
              2 Vote for this issue
              Watchers:
              15 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.