MySQL Indexes

MySQL indexes help the server locate matching rows without scanning every row in a table. A well-chosen index can improve filters, joins, sorting, and grouped queries, but every index consumes storage and adds work to inserts, updates, and deletes.

Create indexes for measured query patterns, not simply for every column. MySQL's optimizer decides whether an available index is cheaper than another access method.

How a MySQL Index Works

InnoDB commonly uses B-tree indexes. Their ordered entries support equality checks, ranges, prefixes, and ordered scans. A primary key also determines how InnoDB organizes table rows, while secondary index entries include the primary-key value.

Create an Index

You can define an index with the table or add it later. Give it a descriptive name that reflects its columns and purpose.

Example:

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    ordered_at DATETIME NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    -- Support customer order-history queries.
    INDEX idx_orders_customer_date (customer_id, ordered_at DESC)
);

-- Add an index to an existing table.
CREATE INDEX idx_orders_status ON orders (status);

Choose Columns from Real Queries

Look at columns used in selective WHERE conditions and join predicates. Columns used for ORDER BY or GROUP BY may also benefit when their order matches the query. A low-selectivity column such as a two-value status may be weak alone but useful inside a composite index.

Understand Composite Index Order

MySQL can use a composite index through its leftmost prefix. An index on (customer_id, status, ordered_at) can support leading combinations that start with customer_id. It generally cannot provide the same lookup for status alone.

CREATE INDEX idx_orders_customer_status_date
ON orders (customer_id, status, ordered_at DESC);

-- Uses the leading customer_id and status parts.
SELECT order_id, ordered_at, total
FROM orders
WHERE customer_id = 204
  AND status = 'SHIPPED'
ORDER BY ordered_at DESC;

Put equality-tested columns first in many common designs, followed by range or ordering columns, but verify the complete workload. Column order must serve the actual filters, joins, and sort direction.

Create a Covering Index

An index covers a query when it contains every column needed to locate and return the result. MySQL may answer it from the index without reading full table rows. Wider indexes cost more storage and maintenance, so include only useful columns.

CREATE INDEX idx_orders_customer_date_total
ON orders (customer_id, ordered_at DESC, total);

-- The selected values are present in the index.
SELECT ordered_at, total
FROM orders
WHERE customer_id = 204
ORDER BY ordered_at DESC
LIMIT 10;

Inspect Indexes

Use SHOW INDEX to review names, uniqueness, column order, cardinality estimates, and visibility.

-- List indexes and their key parts.
SHOW INDEX FROM orders;

Verify Usage with EXPLAIN

EXPLAIN shows the optimizer's planned access. Check possible_keys, the chosen key, used key_len, estimated rows, and the Extra field. A possible index is not necessarily the selected one.

-- Inspect the plan without running the SELECT result.
EXPLAIN
SELECT order_id, total
FROM orders
WHERE customer_id = 204
ORDER BY ordered_at DESC
LIMIT 10;

EXPLAIN ANALYZE runs the statement and reports actual timing, rows, and loops in tree format. Use it carefully: the query really executes.

-- Compare optimizer estimates with actual execution.
EXPLAIN ANALYZE
SELECT order_id, total
FROM orders
WHERE customer_id = 204;

Test with representative data. A plan from a tiny development table may differ from the plan chosen for a large production table.

Use Prefix and Functional Indexes

A prefix index stores the beginning of a string column and can reduce index size. Choose a prefix long enough to distinguish useful values. MySQL also supports functional key parts for deterministic expressions.

-- Index the leading characters of a long URL.
CREATE INDEX idx_pages_url_prefix ON pages (page_url(80));

-- Index a normalized email expression in supported MySQL versions.
CREATE INDEX idx_users_lower_email ON users ((LOWER(email)));

Use Descending Indexes

InnoDB supports descending key parts. They are particularly useful when a multi-column sort mixes ascending and descending directions.

-- Match a category filter and newest-first listing.
CREATE INDEX idx_posts_category_published
ON posts (category_id ASC, published_at DESC);

Test Removal with Invisible Indexes

An invisible index continues to receive updates and enforce uniqueness, but the optimizer ignores it by default. This lets you observe workload changes before dropping a questionable non-primary index.

-- Temporarily hide an index from normal optimizer choices.
ALTER TABLE orders ALTER INDEX idx_orders_status INVISIBLE;

-- Restore the index if query performance declines.
ALTER TABLE orders ALTER INDEX idx_orders_status VISIBLE;

Remove an Index

-- Drop an index that has been measured as unnecessary.
DROP INDEX idx_orders_status ON orders;

Common Indexing Mistakes

• Adding duplicate or overlapping indexes without checking existing definitions.
• Ignoring the leftmost-prefix rule in composite indexes.
• Wrapping indexed columns in expressions that do not match a functional index.
• Expecting an index to help when most rows match the condition.
• Measuring only SELECT speed and ignoring write overhead and storage.
• Assuming the optimizer used an index without checking EXPLAIN.

Conclusion

MySQL indexes improve data access when their columns and order match real query patterns. Start with selective filters and joins, design composite indexes around leftmost prefixes, inspect plans with EXPLAIN, and remove redundant indexes carefully. Balanced indexing improves reads without placing unnecessary cost on every write.