GIN Index vs MySQL: How Postgres Indexes JSON, Arrays, and Full-Text
People searching “GIN vs MySQL” usually want one answer: does MySQL have anything like the Postgres GIN index? Here is the honest, vendor-neutral comparison, verified against the PostgreSQL and MySQL documentation.
MySQL has no GIN index. There is no single equivalent.
GIN (Generalized Inverted Index) is a PostgreSQL access method that maps each component of a value (a word, an array element, a JSON key/value, a trigram) to the rows that contain it. One GIN index on a JSONB column answers many different containment and existence queries. MySQL reaches the same destinations with three narrower, separate tools: FULLTEXT indexes for text, multi-valued indexes (8.0.17+) for JSON arrays, and functional / generated-column indexes for specific JSON paths. The practical difference: in MySQL you must declare each path you want indexed; in Postgres one GIN index covers arbitrary paths.
What replaces GIN in MySQL, use case by use case
Verified against the PostgreSQL 18 and MySQL 8.4 documentation, June 2026. “Edge” flags where one engine has a structural advantage, not a benchmark winner.
| Use case | PostgreSQL | MySQL | Edge |
|---|---|---|---|
| Arbitrary JSON-path containment / existence | GIN on JSONB (jsonb_ops or jsonb_path_ops) - one index answers @>, ?, ?|, ?& | Multi-valued index (8.0.17+) for arrays; generated-column or functional index per scalar path | Postgres |
| JSON array membership | GIN on JSONB or array column - element lookups at index speed | Multi-valued index + MEMBER OF(), JSON_CONTAINS(), JSON_OVERLAPS() | Even |
| Full-text document search | GIN on tsvector - dictionaries, stemming, ranking, weighting | FULLTEXT index (InnoDB since 5.6) - natural-language + boolean modes | Even |
| Substring / fuzzy match (LIKE '%term%', typo tolerance) | GIN or GiST with pg_trgm (trigram) - indexed substring + similarity() | No indexed equivalent - FULLTEXT is word-based; LIKE '%term%' scans | Postgres |
| Array containment (tags, labels, ints) | GIN on a native array column - @>, &&, <@ | No native array type; model as JSON array + multi-valued index, or a join table | Postgres |
| Plain equality / range / sort | B-tree (default) | B-tree (InnoDB default) - clustered on the primary key | Even |
| Geospatial | GiST / SP-GiST (PostGIS) - the industry standard | SPATIAL (R-tree) index on geometry types | Postgres |
JSON: one GIN index vs one index per path
In Postgres, a single GIN index on a jsonb column makes containment and key-existence queries fast across the whole document. The jsonb_ops operator class supports @>, ?, ?|, and ?&; the smaller jsonb_path_ops variant supports @> only but builds a smaller, faster index. You do not name the paths in advance.
MySQL takes the opposite approach: you index the paths you query. For JSON arrays, a multi-valued index (InnoDB, MySQL 8.0.17 and later) stores one index record per array element and accelerates MEMBER OF(), JSON_CONTAINS(), and JSON_OVERLAPS(). For scalar paths you add a generated column or a functional index on an expression such as CAST(custinfo->'$.zipcode' AS UNSIGNED). It works well when your query paths are known and stable; it is more schema engineering when they are ad hoc.
Full-text: tsvector + GIN vs FULLTEXT
Both engines use an inverted index for document search, so for most apps this is a wash. Postgres indexes a tsvector column with GIN (or GiST) and gives you configurable dictionaries, stemming, ranking (ts_rank), and per-field weighting, at the cost of a little more setup. MySQL FULLTEXT indexes (InnoDB since 5.6) give you natural-language and boolean search modes with almost no setup. If you need rich linguistic control, Postgres has more knobs; if you want word search working in one CREATE FULLTEXT INDEX, MySQL is simpler. Neither is a dedicated search engine; past a certain scale, both teams reach for OpenSearch / Elasticsearch.
The one MySQL genuinely cannot match: trigram substring search
This is the clearest structural gap. With the pg_trgm extension, Postgres can put a GIN or GiST index behind LIKE '%term%', ILIKE, regex, and fuzzy similarity() matching. MySQL has no indexed equivalent: FULLTEXT is word-based (it cannot match an arbitrary substring inside a word), and a leading-wildcard LIKE '%term%' always scans. If indexed substring or typo-tolerant search on a column is a core feature, that is a real reason to favour Postgres.
Does this decide Postgres vs MySQL?
Usually not on its own. If your JSON query paths are known and stable, MySQL's multi-valued and functional indexes are perfectly adequate, and the rest of your stack (LAMP fit, Vitess, team depth) may matter far more. GIN becomes a deciding factor when you query arbitraryJSON shapes, need indexed substring or fuzzy search, or lean on native array containment. Those are the workloads where Postgres's indexing model removes schema-engineering work that MySQL would require.
Weigh it inside the full picture: the workload decision matrix, the 2026 benchmark data, and the feature-parity comparison. Always test indexing strategy against your own query patterns before committing.