3 ms·

> 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 "virt

> 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 7y ago
> 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 7y ago
  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.)

hans_castorp 7y ago

> 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 7y ago
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.)