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

Optimizer support for Multi-Valued indexes over JSON data

    XMLWordPrintable

Details

    Description

      MDEV-40168: Indexes over JSON data based on Fulltext index enables indexing for JSON data with a fulltext index.
      Use of fulltext index here is a temporary measure until MDEV-25848: Support for Multi-Valued Indexes is done.

      Once we have both MDEV-40168 and MDEV-25848, we will switch to using Multi-Value index. The switch is covered by MDEV-40113: Optimizer support for JSON queries: switch to using Multi-Valued Index API

      Fulltext->MVI switch allows richer querying capabilities

      It looks like use of Multi-Value Index instead of fulltext will allow to expand the class of sargable conditions.
      Basically, fulltext boolean mode searches allow to search for exact match (or a prefix), while Multi-Value Index allows range searches.

      fulltext searches allow narrow searches for equality conditions

      select * from t where JSON_VALUE(col, '$.memb1')='val1'

      while MVI could allow non-equality searches:

      select * from t where JSON_VALUE(col, '$.memb1')>1000

      (with fulltext, depending on the index type we could enumerate rows that have non-empty $memb1. But filtereing on >1000 doesn't seem to be possible)

      Attachments

        Issue Links

          includes

          Task - A task that needs to be done. MDEV-40113 Optimizer support for JSON queries: switch to using Multi-Valued Index API

          • Critical - Crashes, loss of data, severe memory leak.
          • Open
          is blocked by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-25848 Support for Multi-Valued Indexes

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

          New Feature - A new feature of the product, which has yet to be developed. MDEV-40168 Indexes over JSON data based on Fulltext index

          • Critical - Crashes, loss of data, severe memory leak.
          • Stalled
          is duplicated by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-35389 Native Multi-Value Indexes for JSON

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

          New Feature - A new feature of the product, which has yet to be developed. MDEV-13594 Support for JSON operators column->path and column->>path

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

          Task - A task that needs to be done. MDEV-37288 JSON Comparator: does MariaDB need one?

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-38591 MEMBER OF json operator

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

          Activity

            People

              ycp Yuchen Pei
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - 40d
                  40d
                  Remaining:
                  Time Spent - 6h 53m Remaining Estimate - 15d
                  15d
                  Logged:
                  Time Spent - 6h 53m Remaining Estimate - 15d Time Not Required
                  6h 53m

                  Git Integration

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