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

Add manual support for indexes

    XMLWordPrintable

Details

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

    Description

      Once MDEV-6017, that adds support for indexes on expressions, is added, it will be straightforward for nosqlprotocol to support indexes. It will be sufficient to enable the creation of indexes, and the server will automatically use any indexes that exists.

      Unfortunately, it is unclear when that support will be available. Meanwhile, indexes could be supported by moving a part of the responsibility to the client.

      A command like

      > db.runCommand({createIndexes: "cars", indexes: [ { key: { Make: 1}, name: "Make" }]});
      

      could be turned into the following SQL

      ALTER TABLE `test`.`cars` ADD COLUMN Make TINYTEXT AS (JSON_COMPACT(JSON_EXTRACT(doc, '$.Make')));
      CREATE INDEX Make ON `test`.`cars` (Make);
      

      This would be quite straightforward to do. Now, with such an index present, a command like

      db.runCommand({
          find: "cars"
          filter : { "Make": "Toyota" }
      });
      

      that currently is basically converted into

      SELECT doc FROM `test`.`cars` WHERE JSON_VALUE(doc, '$.Make') = "Toyota";
      

      should be converted into

      SELECT doc FROM `test`.`cars` WHERE Make = "Toyota";
      

      The problem is, that in order to be able to do that, it must be known that a Make virtual column exists and becoming aware of that is somewhat messy at best. That problem could be circumvented, by moving the problem to the client, for instance, like:

      db.runCommand({
          find: "cars",
          filter : { "Make": "Toyota" },
          comment: { mariadb_indexes: [ "Make" ] }
      });
      

      That is, the client must specify what fields are indexed. The reason for using comment for this purpose, is that the command will continue to be valid for MongoDB as well, which it would not be if a custom field is added. Further, once indexes on expressions are supported, nosqlprotocol can also ignore the comment.

      A proof of concept implementation that validates the idea exists.

      A full implementation implies supporting the commands createIndexes, dropIndexes and listIndexes, and updating the code that converts a filter to a WHERE-clause.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              johan.wikman Johan Wikman
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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