[darrenf]

> And as far as I can remember MySQL still doesn't support partial/expression indexes, which is a deal breaker for me. Especially in my json heavy workloads where being able to index specific json paths is critical for performance.

Do generated column indexes meet this need?

    CREATE TABLE json_with_id_index (
       json_data JSON,
       id        INT GENERATED ALWAYS AS (json_data->"$.id"),

       INDEX id (id)
    )

https://dev.mysql.com/doc/refman/8.0/en/create-table-seconda...

[dissident_coder]

I suppose this is a decent workaround for certain things (i've used it in sqlite before), the main kind of index i'm using with postgres jsonb looks something like this

    create index on my_table(document ->> 'some_key') where (document ? 'some_key' AND document ->> 'some_key' IS NOT NULL);

you can use generated columns to get around the first part of the index, but you can't have the WHERE part of the index in mysql as far as I am aware (but it has been a very long time since I've worked with it so I'm prepared to be wrong).

[simcop2387]

Looks like that would work as an expression index, though i can't tell at a glance if this requires the column to also be stored which would increase storage size (but probably isn't a huge problem if it is). But that likely won't work for dealing with the partial index case where you're only wanting to keep the ones that aren't null in the index to reduce the size (and speed up null/not null checks).

[evanelias]

MySQL supports indexing expressions directly, which is effectively the same as indexing an invisible virtual column: https://dev.mysql.com/doc/refman/8.0/en/create-index.html#cr...

MySQL supports "multi-valued indexes" over JSON data, which offer a non-obvious solution for partial indexes, since "index records are not added for empty arrays": https://dev.mysql.com/doc/refman/8.0/en/create-index.html#cr...

MariaDB doesn't support any of this directly yet though: https://www.skeema.io/blog/2023/05/10/mysql-vs-mariadb-schem...