Advanced indexing: composite, covering and FULLTEXT

Order composite indexes deliberately, make hot queries covered, and reach for prefix, functional, invisible and FULLTEXT indexes when a plain B-tree is not the right shape.

Composite order, covering and prefix indexes

A composite index is a sorted structure over several columns in a fixed order, usable from the left. Within that rule you still have choices: put equality columns before range columns, and consider making the index cover the query so the table rows are never visited.

-- equality first, then the range
CREATE INDEX idx_orders_status_placed ON orders (status, placed_at);

-- covering: every column the query needs is in the index
CREATE INDEX idx_orders_cover ON orders (customer_id, placed_at, total);
EXPLAIN SELECT customer_id, placed_at, total
FROM orders
WHERE customer_id = 42
ORDER BY placed_at DESC;                 -- Extra: Using index

-- prefix index on a long text column, when the full value is too wide
CREATE INDEX idx_users_email_prefix ON users (email(20));

-- descending index (8.0+), useful for an equality prefix with a DESC sort
CREATE INDEX idx_events_desc ON events (actor_id, created_at DESC);

FULLTEXT, functional and invisible indexes

ALTER TABLE articles ADD FULLTEXT INDEX ft_articles (title, body);

SELECT id,
       MATCH(title, body) AGAINST ('connection pool' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST ('connection pool' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 10;

-- ngram parser: needed for scripts that do not separate words with spaces
ALTER TABLE articles
  ADD FULLTEXT INDEX ft_body_ngram (body) WITH PARSER ngram;

-- hide an index from the optimiser without dropping it
ALTER TABLE orders ALTER INDEX idx_orders_cover INVISIBLE;

-- index an expression instead of the raw column (8.0.13+)
ALTER TABLE users ADD INDEX idx_lower_email ((LOWER(email)));

• MATCH ... AGAINST must appear in both SELECT and WHERE for the score to be usable for ordering.
• Full-text indexes have their own relevance ranking and stopword list; the default minimum token length makes short words unsearchable until you change innodb_ft_min_token_size.
• A functional index is only used when the query contains the same expression. If you index LOWER(email), a predicate on the bare email column will not seek.

Statistics and index maintenance

• ANALYZE TABLE refreshes the cardinality estimate. An estimate that is badly wrong is worse than no estimate, because the optimiser will confidently choose a plan that looks cheap and is not.
• A histogram over a non-indexed column improves selectivity estimates for skewed data. The depth of per-index statistics is controlled by innodb_stats_persistent_sample_pages, and more pages means slower but better statistics.
• Index merge lets the optimiser combine two indexes with UNION or INTERSECT. It is a fallback: one correctly ordered composite index almost always beats it.
• An index that is a strict left prefix of another is redundant. (a) adds nothing on top of (a, b) and still costs writes.
• Reads scale with indexes, writes pay for them. performance_schema.table_io_waits_summary_by_index_usage shows which indexes have never been read.

FAQ

Related

Indexes and reading EXPLAIN Query optimisation and diagnostics

Last refreshed 2026-09-18.