[hans_castorp]

Indexing a generated/computed column is not the same as creating an index on an expression. If you want to support several different expressions you need to create a new column each time.

Additionally, an ALTER TABLE blocks access to the table. Indexes can be created concurrently while other transactions can still read and write the table.

But MySQL doesn't support indexing the complete JSON value for arbitrary queries. You can only index specific expressions by creating a computed column with that expression and indexing that.

[evanelias]

> If you want to support several different expressions you need to create a new column each time

Yes and no. Generated columns in MySQL can optionally be "virtual". An indexed virtual column is functionally identical to an index on an expression.

> Additionally, an ALTER TABLE blocks access to the table.

It depends substantially on the specific ALTER and version of MySQL. Many ALTERs do not block access to the table in modern MySQL; some are even instantaneous.

> But MySQL doesn't support indexing the complete JSON value for arbitrary queries. You can only index specific expressions by creating a computed column with that expression and indexing that.

What's the difference, functionally speaking? (Asking honestly, not being snarky -- I may not understand what you are saying / what the equivalent postgres feature is?)

[hans_castorp]

> What's the difference, functionally speaking? (Asking honestly, not being snarky -- I may not understand what you are saying / what the equivalent postgres feature is?)

You create a single index, e.g:

create index on the_table using gin(jsonb_column);

And that will support many different types of conditions,

e.g.: check if a specific key/value combination is contained in the JSON:

where jsonb_column @> '{"key": "value"}'

this also works with nested values:

where jsonb_column @> '{"key1" : {"key2": {"key3": 42}}}'

Or you can check if an array below a key contains one or multiple values:

where jsonb_column @> '{"tags": ["one"]}' or where jsonb_column @> '{"tags": ["one", "two"]}'

Or you can check if all keys from a list of keys are present:

where jsonb_column ?& array['key1', 'key2']

All those conditions are covered by just one index.

[evanelias]

Interesting, thanks! Indeed, there isn't an equivalent feature in MySQL yet.

Out of curiosity, how commonly is this used at scale? I'd imagine there are significant trade-offs with write amplification, meaning it would consume a lot of space and make writes slow. (vs making indexes on specific expressions, I mean. That said, you're right -- there are definitely use-cases where making indexes on specific expressions isn't practical or is too inflexible.)