Indexes: why one query is slow

MySQL Course · lesson 6 of 21 · 5 min read

An index is a sorted lookup structure - and the wrong one is worse than none.

Open this lesson in the learning hub

Key points

  • Without an index the database reads every row to answer a WHERE - a full table scan.
  • A B-tree index keeps the column sorted, so a lookup is a few jumps instead of a full scan.
  • Indexes cost write time and disk: every INSERT and UPDATE must maintain them.
  • A composite index on (a, b) helps queries filtering on a, or a and b - but not b alone.
  • Wrapping a column in a function (WHERE YEAR(joined) = 2024) usually stops the index being used.

Example

-- see what MySQL plans to do
EXPLAIN SELECT * FROM orders WHERE customer_id = 3;

-- type: ALL      => full table scan, no index used
-- type: ref      => index lookup, good

CREATE INDEX idx_orders_customer ON orders(customer_id);

Read EXPLAIN before adding an index - the fix is often the query, not a new index.

GROUP BY and aggregates Transactions and ACID

This is a reading copy. The full lesson — with the visual explainer, the interactive lab and a Run button for the code — lives in the MySQL Course course, and every lesson in it is listed on the MySQL Course contents page.