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.