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

Feature Request: Allow for indexes to be created on the information_schema tables

Details

    Description

      Hello,
      I have a customer who has run into an issue when they have data they want to query in information_schema tables but the tables have 100,000's+ records in them. The more records that get added the worse their query performs.
      Could it be possible to set up a way to allow indexes be created against tables in the information_schema? Whether it be allowing 

      alter table add index...

      or some way to specify it in the MariaDB config file.
      These indexes should not be created by default, and only by users who feel they need them.

      Attachments

        Issue Links

          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-11655 Transactional data dictionary

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-17581 Data dictionary

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-20609 Full table scan in INFORMATION_SCHEMA.PARAMETERS/ROUTINES

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

          Activity

            Ascending order - Click to sort in descending order
            danblack Daniel Black added a comment -

            Because of the way the information_schema is written, its currently easier to write in specific optimizations like those that exists for information_schema.tables when in a specific database/table.

            If you provide a more specific information_schema table request and what sort of look ups are being performed maybe an optimization can be achieved in a shorter development time.

            Assuming this request is table related, the alternative is for the data dictionary aspects to be implemented and for this to become a real table, which would have greater support for arbitrary indexes.

            danblack Daniel Black added a comment - Because of the way the information_schema is written, its currently easier to write in specific optimizations like those that exists for information_schema.tables when in a specific database/table. If you provide a more specific information_schema table request and what sort of look ups are being performed maybe an optimization can be achieved in a shorter development time. Assuming this request is table related, the alternative is for the data dictionary aspects to be implemented and for this to become a real table, which would have greater support for arbitrary indexes.
            kyle.hutchinson Kyle Hutchinson added a comment -

            The customer is using two Java functions to verify that the data being sent to the procedures is a valid data type. getProcedures & getProcedureColumns are the methods they are using.

            kyle.hutchinson Kyle Hutchinson added a comment - The customer is using two Java functions to verify that the data being sent to the procedures is a valid data type. getProcedures & getProcedureColumns are the methods they are using.
            diego dupin Diego Dupin added a comment -

            this is exactly MDEV-20609

            diego dupin Diego Dupin added a comment - this is exactly MDEV-20609
            serg Sergei Golubchik added a comment -

            information_schema "tables" are not tables as such, but views into system data. Views cannot have user-defined indexes.

            serg Sergei Golubchik added a comment - information_schema "tables" are not tables as such, but views into system data. Views cannot have user-defined indexes.

            People

              serg Sergei Golubchik
              kyle.hutchinson Kyle Hutchinson
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.