[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...