Chapter 08 · Index Engineering and Access Path Design
Functional, Multi-Valued, FULLTEXT, and Spatial Index Use Cases
Use specialized index families only for the problems they actually solve: deterministic expressions, JSON-array membership, token search, and spatial relationships—without treating them as interchangeable B-tree variants.
Learning outcomes
“Index” is an umbrella word. A functional B-tree key, a multi-valued JSON-array index, an InnoDB FULLTEXT inverted index, and a spatial R-tree solve different search problems. Using the wrong family can be worse than having no index because it adds maintenance cost without matching the predicate semantics.
Create and verify a functional key part for a deterministic expression and understand the expression-matching requirement.
Use an InnoDB multi-valued index for JSON-array membership predicates while respecting its important restrictions.
Distinguish FULLTEXT token/relevance search from substring matching and normal B-tree prefix search.
Create an SRID-restricted spatial column and R-tree SPATIAL index for minimum-bounding-rectangle predicates.
Use SHOW INDEX and EXPLAIN evidence to prove that each specialized index is solving the intended predicate rather than existing unused.
Functional key parts: index the value you actually search
ServiceHub stores an SLA target inside JSON metadata. Scanning and converting that JSON expression for every row is avoidable when the application repeatedly filters on the same deterministic expression. MySQL functional key parts are implemented using hidden virtual generated columns; the index itself still consumes normal index storage and carries generated-column restrictions.
USE servicehub_index_lab;CREATE INDEX ix_wo_sla_minutes ON work_orders ((CAST(metadata->>'$.sla_minutes' AS UNSIGNED)));ANALYZE TABLE work_orders;SHOW INDEX FROM work_orders WHERE Key_name='ix_wo_sla_minutes';EXPLAIN ANALYZESELECT work_order_id, tenant_id, metadata->>'$.sla_minutes' AS sla_minutesFROM work_ordersWHERE CAST(metadata->>'$.sla_minutes' AS UNSIGNED) <= 60LIMIT 50;The expression in the query must be semantically compatible with the indexed expression. Changing types, collations, or expression structure can make a seemingly equivalent predicate fail to match the functional index. That is why functional indexes should be documented together with the exact application expression they are intended to serve.
Functional key parts inherit generated-column restrictions. Subqueries, variables, stored functions, and other disallowed generated-column constructs cannot simply be wrapped in double parentheses to become indexable.
Multi-valued indexes: one row, several JSON-array index records
A normal secondary index has one index record per indexed row. A multi-valued index can generate multiple secondary index records for one InnoDB row, making it suitable for selected membership predicates over JSON arrays.
CREATE INDEX ix_wo_skill_codes ON work_orders ((CAST(metadata->'$.skill_codes' AS UNSIGNED ARRAY)));ANALYZE TABLE work_orders;SHOW INDEX FROM work_orders WHERE Key_name='ix_wo_skill_codes';EXPLAIN ANALYZESELECT work_order_id,tenant_id,metadata->'$.skill_codes' AS skill_codesFROM work_ordersWHERE 12 MEMBER OF (metadata->'$.skill_codes')LIMIT 50;EXPLAINSELECT work_order_idFROM work_ordersWHERE JSON_OVERLAPS(metadata->'$.skill_codes', JSON_ARRAY(12,22));MySQL can use multi-valued indexes for suitable MEMBER OF(), JSON_CONTAINS(), and JSON_OVERLAPS() predicates. They do not support ordering, cannot be primary keys, cannot be covering indexes, and do not provide general JSON-path acceleration. A JSON document with many unrelated ad hoc paths is not automatically “indexed” because one array has a multi-valued key.
FULLTEXT: token search is not LIKE with a faster index
Dispatchers also search incident narratives by words. A B-tree is not designed for arbitrary token relevance. InnoDB FULLTEXT indexes build an inverted-search structure over CHAR, VARCHAR, and TEXT columns and are queried with MATCH() ... AGAINST().
CREATE FULLTEXT INDEX ft_wo_text ON work_orders(summary,details);SELECT work_order_id, MATCH(summary,details) AGAINST('network diagnostic' IN NATURAL LANGUAGE MODE) AS score, summaryFROM work_ordersWHERE MATCH(summary,details) AGAINST('network diagnostic' IN NATURAL LANGUAGE MODE)ORDER BY score DESCLIMIT 10;EXPLAINSELECT work_order_idFROM work_ordersWHERE MATCH(summary,details) AGAINST('network' IN NATURAL LANGUAGE MODE);Tokenization, stopwords, minimum token size, parser choices, language, and relevance mode change FULLTEXT semantics. A full-text index is not a substitute for exact equality, prefix ordering, or a dedicated search engine when you need advanced ranking, analyzers, typo tolerance, or distributed search.
Adding a normal B-tree prefix index on details(20) does not make LIKE '%network%' into a general token-search index. The unknown leading characters prevent an ordinary left-edge B-tree range, while FULLTEXT solves a different token-search problem.
SPATIAL: geometry needs geometric indexing semantics
For a local lab we use Cartesian coordinates with SRID 0 to avoid distracting geographic axis-order issues. The column is NOT NULL and explicitly SRID-restricted so the optimizer can safely use the spatial index. InnoDB SPATIAL indexes are R-trees built around geometry minimum bounding rectangles (MBRs), not ordinary lexicographic B-trees.
DROP TABLE IF EXISTS service_sites;CREATE TABLE service_sites ( site_id INT UNSIGNED NOT NULL PRIMARY KEY, site_name VARCHAR(100) NOT NULL, location POINT NOT NULL SRID 0, SPATIAL INDEX sx_sites_location (location)) ENGINE=InnoDB;INSERT INTO service_sites VALUES (1,'North Hub',ST_SRID(POINT(10,10),0)), (2,'East Clinic',ST_SRID(POINT(25,12),0)), (3,'Warehouse',ST_SRID(POINT(70,80),0)), (4,'Library',ST_SRID(POINT(18,22),0));SET @box = ST_GeomFromText('POLYGON((0 0,40 0,40 40,0 40,0 0))',0);EXPLAINSELECT site_id,site_name FROM service_sitesWHERE MBRContains(@box,location);SELECT site_id,site_name FROM service_sitesWHERE MBRContains(@box,location) ORDER BY site_id;MBR predicates reason about bounding rectangles. Exact geometric predicates can have different semantics and costs. When moving from SRID 0 to real geographic coordinates, explicitly choose the spatial reference system and verify function semantics rather than treating longitude/latitude as generic X/Y numbers.
One diagnostic inventory, four index families
SHOW INDEX FROM work_orders;SHOW INDEX FROM service_sites;SELECT TABLE_NAME,INDEX_NAME,INDEX_TYPE,COLUMN_NAME,EXPRESSION,IS_VISIBLEFROM information_schema.STATISTICSWHERE TABLE_SCHEMA='servicehub_index_lab' AND TABLE_NAME IN ('work_orders','service_sites')ORDER BY TABLE_NAME,INDEX_NAME,SEQ_IN_INDEX;| Index family | Fits | Does not magically solve |
|---|---|---|
| Functional B-tree | Repeated deterministic expression comparisons/ranges. | Arbitrary different expressions or nonindexable generated-column logic. |
| Multi-valued JSON | Supported JSON array membership/overlap predicates. | General JSON document search, range ordering, covering scans. |
| FULLTEXT | Token/relevance search over text columns. | Exact ordered B-tree access, arbitrary search-engine features. |
| SPATIAL R-tree | Spatial MBR relationship pruning for indexed geometries. | Ordinary scalar ordering or nonspatial predicates. |
Knowledge check
- Why do functional indexes require care with the query expression?
- What makes a multi-valued index different from a normal secondary index?
- Can a multi-valued index be a covering index?
- Why is FULLTEXT not equivalent to a B-tree on a text prefix?
- What index structure does InnoDB use for SPATIAL indexes?
Reveal answers
- The optimizer must recognize a compatible expression; type/collation/expression differences can prevent the functional key from matching.
- One data row can contribute multiple index records, such as one per JSON-array element.
- No. MySQL documents that multi-valued indexes cannot be covering indexes and do not support ordering/range scans in the normal way.
- FULLTEXT uses token/inverted-search semantics and MATCH...AGAINST; a B-tree prefix represents ordered leading characters.
- An R-tree over spatial bounding information, not the normal B-tree used for most scalar indexes.
Production judgment and bridge
Specialized indexes deserve an even higher evidence bar because their maintenance and semantics are less obvious to application teams. Document the predicate they serve, the data type/collation/SRID/parser assumptions, the plan evidence, and what feature-specific limitations apply. Do not add all four families to a table “for future flexibility.”
Lesson 4 now looks inside optimizer execution choices that can make an existing B-tree more effective without adding another index: Index Condition Pushdown, loose/tight grouping scans, and skip scan.