What Are Indexes in MySQL

An index is a separate data structure that speeds up lookups in a table. Without an index MySQL scans every row (Full Table Scan). With the right index a lookup takes logarithmic time — O(log n) instead of O(n).

Index Types

Type	Description	When to Use
`PRIMARY KEY`	Unique, NOT NULL, one per table	Main row identifier
`UNIQUE`	Prevents duplicates	Email, username, order number
`INDEX / KEY`	Regular non-unique index	Columns in WHERE, ORDER BY, JOIN
`FULLTEXT`	Full-text search	Text search (an alternative to LIKE)
`SPATIAL`	Spatial	Coordinates, geolocation

Creating Indexes

-- When creating the table
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_status_created (status, created_at)
);

-- Add to an existing table
ALTER TABLE orders ADD INDEX idx_status (status);

-- Unique index
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- Composite index
CREATE INDEX idx_user_status ON orders(user_id, status);

Column Order Rule for Composite Indexes

A composite index (a, b, c) works for the queries: WHERE a, WHERE a AND b, WHERE a AND b AND c. But it does NOT work for: WHERE b, WHERE c, WHERE b AND c — the leftmost column must be in the condition.

-- This index (user_id, status) helps:
SELECT * FROM orders WHERE user_id = 5 AND status = 'pending';
SELECT * FROM orders WHERE user_id = 5;

-- But it does NOT help:
SELECT * FROM orders WHERE status = 'pending';

Analyzing a Query with EXPLAIN

EXPLAIN SELECT * FROM orders WHERE user_id = 5 AND status = 'pending'\G

-- Bad: type=ALL (full table scan)
-- Good: type=ref or type=eq_ref

Viewing Table Indexes

SHOW INDEX FROM orders;

-- Or via information_schema:
SELECT INDEX_NAME, COLUMN_NAME, CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';

Dropping Indexes

DROP INDEX idx_status ON orders;
ALTER TABLE orders DROP INDEX idx_status;

Do not index every column — each index slows down INSERT/UPDATE/DELETE and takes up disk space. Only add indexes that really speed up your queries.

When an Index Does Not Help

An index is not used by the optimizer even when it exists if the query applies a function to the indexed column: WHERE YEAR(created_at) = 2026 forces the server to compute YEAR() for every row, completely ignoring the index on created_at. The same happens with WHERE status LIKE '%pending%' — a leading % wildcard cannot use a B-Tree index, because sort order does not help find a match in the middle of a string. Implicit type conversion is another common cause: comparing a VARCHAR column to a number without quotes can trigger a full scan instead of using the index.

Comparing the Cost: Index on Read vs Write

Operation	Without an Index	With an Index
SELECT by condition	Full Table Scan, O(n)	O(log n), many times faster on large tables
INSERT one row	Only writes the data	Extra write to the B-Tree of every index
UPDATE an indexed column	Only updates the data	Rebuilds the row's position in every affected index

Reading EXPLAIN Output Line by Line

EXPLAIN SELECT * FROM orders WHERE user_id = 5 AND status = 'pending'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
         type: ref
possible_keys: idx_user_status
          key: idx_user_status
      key_len: 4
          ref: const
         rows: 12
        Extra: Using index condition

possible_keys is the list of indexes the optimizer considered; key is the one it actually picked. If key is empty while possible_keys is not, the optimizer decided a full scan was cheaper — usually the case when the selection covers most of the table. rows is an estimate of how many rows will be examined, not guaranteed to be exact but useful for comparing query variants.

FULLTEXT and SPATIAL Indexes: Where They Fall Short

A FULLTEXT index only works with InnoDB (since MySQL 5.6) and MyISAM, it does not replace a full search engine like Elasticsearch or Sphinx for large volumes of text, and it cannot rank relevance as flexibly. For a site with a few thousand articles, FULLTEXT is more than enough; for millions of documents with faceted search, move search to an external engine and keep MySQL as the source of truth for the data.

Checking Index Size and Usage

-- Size of each table index in megabytes
SELECT index_name, ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE table_name = 'orders' AND stat_name = 'size';

-- Indexes that have never been used since the server started
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star = 0
ORDER BY object_schema, object_name;

The second query requires performance_schema to be enabled and shows unused indexes — candidates for removal unless they back a UNIQUE constraint. A detailed breakdown of optimizing individual queries is in the article on reading a query plan, and diagnosing already-run slow queries is covered in the article on Slow Query Log.