MySQL Indexing Explained: How Indexes Speed Up Your Queries

A slow query is usually a missing index. This guide explains how MySQL indexes actually work, how to read EXPLAIN, and how to add the right indexes without overdoing it.

RH Raja HunainAuthor 20 Nov 2026 4 min read 50 views Updated 21 Nov 2026
Table of Contents
Share

If your application feels slow, the database is the first place to look — and a missing index is the most common cause. An index can turn a query that scans a million rows into one that reads three or four. This guide explains indexing in plain language, with examples you can run on any MySQL 8 database.

What Is an Index, Really?

Think of a database table as a thick book with no table of contents. To find one topic, you flip through every page. An index is the contents page at the front: a separate, sorted structure that tells MySQL exactly where a row lives, so it can jump straight to it.

MySQL stores most indexes as a B-tree: a balanced tree that stays sorted and can be searched very quickly, even with tens of millions of rows.

See the Problem with EXPLAIN

Never guess — measure. Put EXPLAIN in front of any SELECT and MySQL shows its plan:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

If the type column says ALL, MySQL is scanning the whole table (a full table scan). The rows column shows roughly how many rows it expects to examine. On a small table that is fine. On a growing orders table, it is a time bomb.

Adding Your First Index

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Run the same EXPLAIN again. The type should now be ref, key should show your new index, and rows should drop dramatically — often from hundreds of thousands to just the handful of orders that customer actually has.

In Laravel, add indexes in your migrations so they are never forgotten:

Schema::table('orders', function (Blueprint $table) {
    $table->index('customer_id');
});

Composite Indexes and the Leftmost Rule

One index can cover several columns:

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

Column order matters. MySQL can use this index for queries filtering on customer_id alone, or on customer_id and status — but not efficiently for status alone. This is the leftmost prefix rule: an index is useful from its leftmost column onward. Put the column you filter by most selectively first.

When You Should NOT Add an Index

Indexes are not free:

  • Every insert and update gets slower, because each index must be updated too.
  • Indexes use disk and memory. Ten indexes on one table is usually a smell.
  • Tiny tables do not need them — scanning 50 rows is already instant.
  • Columns with very few distinct values (like a yes/no flag alone) rarely benefit.

Index for the queries you actually run, not for every column "just in case".

Five Indexing Mistakes to Avoid

  1. No index on foreign keys — joins and WHERE customer_id = ... crawl without one.
  2. Wrapping an indexed column in a function — WHERE DATE(created_at) = '2026-01-01' cannot use the index. Compare ranges instead: created_at >= '2026-01-01' AND created_at < '2026-01-02'.
  3. Duplicate indexes — an index on (customer_id) is redundant if (customer_id, status) already exists.
  4. Indexing but never checking — run EXPLAIN after adding an index and confirm the plan changed.
  5. Ignoring slow queries until production — enable the slow query log in development and fix problems early.

A Simple Checklist

  • Every foreign key column: indexed.
  • Columns in frequent WHERE, ORDER BY and GROUP BY clauses: considered.
  • Composite indexes ordered by the leftmost rule.
  • Every important query verified with EXPLAIN.
  • No duplicate or unused indexes (check with SHOW INDEX FROM table_name).

Slow database queries are exactly the kind of problem this blog collects — browse the errors and fixes library for step-by-step solutions, or read how these lessons show up in real projects in the REST API tutorial. More database guides live in the MySQL Databases category.

Was this helpful? 0 likes Login to like & bookmark
RH

Written by

Raja Hunain

Full Stack Developer at Raja Dev Solutions — writes about real development work, the errors he faced and exactly how he fixed them, plus practical Laravel, PHP, JavaScript and MySQL tutorials.

More about the author

Comments (1)

Raja · 21 Nov 2026, 12:47 AM

good

Leave a comment

Comments are moderated and appear after approval.

Keep reading

Related Articles