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

Implement Partial / Filtered Indexes

    XMLWordPrintable

    Details

      Description

      It would be nice, if MariaDB has some way to index only subset of values / rows.

      For example:
      We have 1% of rows value set to "active" and 99% of rows set to "inactive". There is no need to index "inactive" rows. It would be similar to table scan. But indexing "active" rows would work well.

      This feature is called Partial Index or Filtered Index in other databases.
      PostgreSQL:
      https://www.postgresql.org/docs/current/static/indexes-partial.html
      Microsoft SQL Server:
      https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes
      Oracle (it can be created by Function-based index and fact that Oracle doesn't indexing NULL values):
      http://dba-presents.com/index.php/databases/oracle/41-filtered-index-equivalent-in-oracle

      There is no way to create this index in MySQL / Mariadb. Closest thing would be using another table and trigger. This complicate a lot of things and isn't much useful.

      Feature request in MySQL: https://bugs.mysql.com/bug.php?id=76631

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              PavelCibulka PavelCibulka
              Votes:
              16 Vote for this issue
              Watchers:
              18 Start watching this issue

                Dates

                Created:
                Updated: