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

Use indexed virtual columns if such exist



    • New Feature
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 6
    • Icebox
    • nosqlprotocol
    • None


      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.


        Issue Links



              JoeCotellese Joe Cotellese
              johan.wikman Johan Wikman
              0 Vote for this issue
              3 Start watching this issue



                Git Integration

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