Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 40 ยท Indexes and Performance

Creating and Managing Indexes in MariaDB

Create, show, drop and manage MariaDB indexes: primary, unique, composite, prefix and FULLTEXT indexes, IGNORED indexes and online DDL, with runnable index examples.

An index is a separate, sorted structure that lets MariaDB find rows without reading the whole table, just like the index at the back of a book. Without a suitable index, WHERE email = '[email protected]' scans every row; with one, the server jumps almost straight to the match. Indexes are the single biggest lever for read performance, and they also enforce uniqueness.

They are not free: every index uses disk and memory and must be updated by each INSERT, UPDATE of an indexed column, and DELETE. The skill is creating the few indexes your queries really need. This lesson covers the commands; the next one covers choosing indexes with EXPLAIN.

How an index works

InnoDB (the default engine) stores each index as a B-tree: sorted keys with pointers down to the leaf pages. Three facts shape everything else:

  • The table itself is the clustered primary-key B-tree: rows are stored in primary-key order. Choose a short, ever-increasing primary key (INT UNSIGNED AUTO_INCREMENT, or a time-ordered UUID).
  • Every secondary index stores its columns plus the primary key value of the row. So a long primary key makes every index bigger.
  • B-trees support equality, ranges (<, >, BETWEEN), ORDER BY in index order, and LIKE 'prefix%'; they do not help LIKE '%suffix'.

Where does the query look?

No index Read every row
Index Jump to matching keys
Primary key The table is the index

Index = fast reads, slower writes.

1 / 2

Creating indexes

You can define indexes in CREATE TABLE or add them later:

SQL
CREATE TABLE customers (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(190) NOT NULL,
  last_name  VARCHAR(80)  NOT NULL,
  city       VARCHAR(80),
  created_at DATETIME     NOT NULL,
  UNIQUE KEY uq_customers_email (email),
  KEY idx_customers_city_name (city, last_name)
) ENGINE=InnoDB;

CREATE INDEX idx_customers_created ON customers (created_at);
ALTER TABLE customers ADD INDEX idx_customers_name (last_name, id);
CREATE UNIQUE INDEX uq_x ON t (a, b);        -- the pair must be unique
CREATE INDEX IF NOT EXISTS idx_y ON t (c);   -- MariaDB extension
CREATE OR REPLACE INDEX idx_y ON t (c, d);   -- MariaDB extension

Name indexes consistently (idx_table_columns, uq_table_columns). KEY and INDEX are synonyms. Adding an index to a big table with ALTER TABLE is an online operation in InnoDB (reads and writes continue), but it still consumes I/O; do it off-peak and watch replica lag (see replication).

SQL Create an index and see the plan change ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

(The portable engine prints EXPLAIN QUERY PLAN; MariaDB uses plain EXPLAIN, covered next lesson. The principle is the same: SCAN becomes SEARCH ... USING INDEX.)

Showing indexes

SQL
SHOW INDEX FROM customers;
SHOW KEYS FROM customers WHERE Key_name = 'uq_customers_email';
SELECT * FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'customers';
SHOW CREATE TABLE customers\G

Useful columns of SHOW INDEX: Key_name, Seq_in_index (position in a composite index), Column_name, Non_unique (0 means unique), Cardinality (estimated distinct values; run ANALYZE TABLE customers; to refresh), Index_type (BTREE, FULLTEXT, HASH) and Ignored (MariaDB 10.6+).

SQL List the indexes of a table ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Unique indexes and primary keys

A UNIQUE index rejects duplicates. NULL is allowed and several NULLs do not conflict with each other in InnoDB, because NULL is "unknown", not equal to anything. A PRIMARY KEY is a unique index on NOT NULL columns and there is only one per table.

SQL UNIQUE index in action

Press Run (or Ctrl + Enter) to see the result here.

In MariaDB use INSERT ... ON DUPLICATE KEY UPDATE, INSERT IGNORE or REPLACE to handle a duplicate on purpose. Catch error 1062 in application code for the rest.

Composite indexes

An index on (city, last_name) serves queries that filter on city, or on city and last_name, but not queries that filter only on last_name: a composite index is usable from its leftmost column onward (the leftmost-prefix rule). Put equality columns first, then the range or sort column. Details and examples are in EXPLAIN and index design.

Prefix indexes and long text

You can index only the first N characters of a string column to save space: CREATE INDEX idx_title ON articles (title(40));. InnoDB limits an index key to 3072 bytes for DYNAMIC rows (4 bytes per character in utf8mb4, so about 768 characters). A prefix index cannot be used to cover a query or to enforce full-length uniqueness, so choose a length that is selective:

SQL
SELECT COUNT(DISTINCT LEFT(title, 20)) / COUNT(*) AS selectivity FROM articles;

FULLTEXT indexes

For word search inside text use FULLTEXT (InnoDB and Aria) and MATCH ... AGAINST:

SQL
CREATE FULLTEXT INDEX ft_articles ON articles (title, body);

SELECT id, title, MATCH(title, body) AGAINST ('replication backup' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST ('+replication -galera' IN BOOLEAN MODE)
ORDER BY score DESC;

Natural-language mode ranks rows by relevance; boolean mode supports +required, -excluded, "phrases" and word*. Words shorter than innodb_ft_min_token_size (3 by default) and very common stopwords are ignored.

Dropping, disabling and testing

SQL
DROP INDEX idx_customers_city_name ON customers;
ALTER TABLE customers DROP INDEX idx_customers_name;
ALTER TABLE customers ADD INDEX ... , DROP INDEX ...;      -- change in one rebuild
ALTER TABLE customers ALTER INDEX idx_customers_created IGNORED;   -- MariaDB 10.6+
ALTER TABLE customers ALTER INDEX idx_customers_created NOT IGNORED;

An ignored index is still maintained, but the optimizer pretends it does not exist. That lets you test "can I drop this?" safely: ignore it, watch the slow-query log for a day, then drop it for real, or un-ignore it in one instant if a query gets slow. You cannot ignore a primary key.

Find unused indexes with SELECT * FROM sys.schema_unused_indexes; (if the sys schema is installed) or the USER_STATISTICS plugin (userstat=ON, then information_schema.INDEX_STATISTICS).

Index maintenance

  • ANALYZE TABLE t; refreshes statistics so the optimizer picks good plans.
  • OPTIMIZE TABLE t; rebuilds a table and its indexes (rarely needed on InnoDB).
  • Redundant indexes: (a) is made redundant by (a, b), so drop the shorter one unless it is a UNIQUE constraint.
  • Every foreign key needs an index on the child column; InnoDB creates one if missing.
  • Check size: SELECT index_name, stat_value * @@innodb_page_size AS bytes FROM mysql.innodb_index_stats WHERE table_name = 'customers' AND stat_name = 'size';.

Common mistakes

  • Indexing every column "just in case": writes slow down and memory is wasted.
  • Indexing low-cardinality columns alone (a status with 3 values, a boolean) when most rows match.
  • A wrong column order in a composite index.
  • Using a function on the indexed column in WHERE.
  • Using a type mismatch (WHERE phone = 5551234 on a VARCHAR column) which forces conversion and skips the index.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which statement is true about the primary key in InnoDB?

    InnoDB clusters rows by primary key and secondary indexes point to rows via it, so keep the key short.
  2. 2. An index exists on (city, last_name). Which query can use it?

    A composite index is used from its leftmost column; last_name alone is not a leftmost prefix.
  3. 3. What does ALTER TABLE t ALTER INDEX idx IGNORED do?

    IGNORED (MariaDB 10.6+) is a reversible way to check an index is unused before dropping it.
  4. 4. Can a UNIQUE index contain more than one NULL in InnoDB?

    NULL means unknown, so two NULLs do not violate uniqueness.

Summary

  • Indexes are B-trees that speed up reads and enforce uniqueness at the cost of space and write time.
  • CREATE INDEX, ALTER TABLE ... ADD INDEX, SHOW INDEX and DROP INDEX are the core commands; IGNORED makes removal safe to test.
  • Composite indexes follow the leftmost-prefix rule; prefix indexes trade accuracy for size; FULLTEXT handles word search.
  • Index what your queries filter, join and sort on; remove redundant indexes.

Next: EXPLAIN and Index Design