Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-3624

Use indexed virtual columns if such exist

    XMLWordPrintable

Details

    • New Feature
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Do
    • 6
    • N/A
    • nosqlprotocol
    • None

    Description

      The JSON documents are stored in a table containing one "real" column doc whose type is JSON and one virtual column id.

      Now, it is possible to add additional virtual columns to the table. Suppose, for instance, that the table will contain information about cars and that documents often will be searched based on the car make. Then it would be possible to create a virtual column and an index on it:

      ALTER TABLE cars ADD make VARCHAR(32) AS (JSON_VALUE(doc, '$.make'));
      CREATE INDEX cars_make_ix ON cars(make);
      

      However, that index is only used, if the virtual column is explicitly referred to as in

      SELECT * FROM cars WHERE make = 'Toyota'
      

      but not if you use the JSON function expression that defines the column

      SELECT * FROM cars WHERE JSON_VALUE(doc, '$.make') = 'Toyota'
      

      If nosqlprotocol is aware of what indexes have been created on a particular table, then it could, while converting a MongoDB find document into the equivalent SQL, explicitly refer to existing (indexed) virtual columns, instead of using the JSON function expressions.

      That way, the benefit of indexes could be brought to users of nosqlprotocol before the server optimizer is extended to deal with this situation.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              johan.wikman Johan Wikman
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.