3 ms·
How is JSON stored/indexed in MySQL? Is it like JSONB in Postgres with GIN indexing available?
by ralusek 10y ago
How is JSON stored/indexed in MySQL?
Is it like JSONB in Postgres with GIN indexing available?
- greenlet 10y agoi believe you have to create a virtual column on specific document fields and create a secondary index on that similar to what you do with Postgres' expression indices, I do not think MySQL has a general JSON GIN index yet
- morgo 10y agoHere is an example: http://mysqlserverteam.com/indexing-json-documents-via-virtual-columns/ http://mysqlserverteam.com/indexing-json-documents-via-virtu... The optimizer will match JSON expressions to virtual columns w/indexes. So that means you can keep your queries in this form and have them indexed: EXPLAIN SELECT * FROM features WHERE feature->"$.properties.STREET" = 'MARKET'\G
- malisper 10y agoIs there a way to index JSON in MySQL without creating one index per unique field?
- morgo 10y agoThe mapping is not strictly one index per field, but a virtual column expression must map to a MySQL type. So depending on the use-case you may be able to use more complicated expressions to limit indexes. But to expand your question, there are two FRs we are looking at: 1) Inverted (aka FULLTEXT) indexes on JSON documents 2) Multi-value indexes