MariaDB Indexing : B-tree, FULLTEXT, SPATIAL, Hash, Composite, Functional

Deterministic guidance for designing indexes on MariaDB. Use this skill to choose the right index type per engine, order composite columns correctly, index JSON paths through virtual columns, manage descending and IGNORED indexes, and read EXPLAIN to diagnose missing-index symptoms.

Quick Reference

• ALWAYS pick the right index type per engine. InnoDB and Aria default to BTREE ; MEMORY supports HASH and BTREE ; MyISAM and Aria support FULLTEXT and SPATIAL (R-tree) ; InnoDB also supports FULLTEXT (since 10.0.5) and SPATIAL. HASH is NOT available on InnoDB user tables. Verified : KB storage-engine-index-types/.
• ALWAYS put the highest-selectivity equality predicate first in a composite index. Leftmost-prefix rule : INDEX(a, b, c) serves queries with predicates on (a), (a, b), (a, b, c) only. It does NOT serve (b), (c), or (b, c) alone. A composite in the wrong order is no better than no index.
• NEVER index every column "just in case". Every INSERT, UPDATE, and DELETE pays a write-amplification cost for every index touched. Audit with SHOW INDEX FROM t and drop redundant indexes ; INDEX(a) is fully covered by INDEX(a, b) and is redundant.
• NEVER write a functional index directly on an expression in MariaDB. Per D-010, MariaDB JSON is a LONGTEXT alias, not a native binary type. There is no CREATE INDEX ix ON t((JSON_VALUE(doc, '$.k'))) like in MySQL 8 or PostgreSQL. ALWAYS materialise the expression in a VIRTUAL or PERSISTENT generated column first, then index that column. Verified : KB generated-columns/.
• Descending indexes are 10.8+. CREATE INDEX ix ON t (col DESC) avoids Using filesort for ORDER BY col DESC only on 10.8 and later. On 10.6-LTS and 10.11-LTS the DESC keyword parses but is silently ignored (treated as ASC). Verified : KB create-index/ grammar and 10.8 release notes.
• IGNORED indexes (10.6+) are MariaDB's equivalent of MySQL "invisible indexes". Use ALTER TABLE t ALTER INDEX ix IGNORED to hide the index from the optimizer without dropping it. The index is still maintained on writes. ALWAYS toggle IGNORED before DROP INDEX in production to prove the index is not load-bearing. The keyword is IGNORED, NOT INVISIBLE ; MySQL syntax does NOT parse on MariaDB.
• FULLTEXT minimum word length is engine-dependent. InnoDB : innodb_ft_min_token_size default 3. MyISAM / Aria : ft_min_word_len default 4. A search for "AI" returns no rows on MyISAM by default. Verified : KB full-text-index-overview/.
• SPATIAL INDEX requires NOT NULL on the geometry column for InnoDB and MyISAM. The R-tree cannot index NULL geometries. ALWAYS declare geom GEOMETRY NOT NULL.
• Prefix indexes (INDEX(col(N))) are mandatory for TEXT and BLOB, optional for long VARCHAR. A prefix index can NEVER be a covering index : the storage engine has only the prefix, not the full value, so the row must still be fetched for any non-prefix column.
• EXPLAIN signals you must learn to spot : type=ALL = full scan = missing or unused index ; Using filesort = no index satisfies the ORDER BY ; Using temporary = no index satisfies the GROUP BY ; Using index = covering index, no row fetch ; Using index condition = ICP push-down, good. Verified : KB explain/.

Decision Trees