MEPX
Chapter 9 of 10All chapters
  1. 1What SQL is
  2. 2SELECT and WHERE
  3. 3NULL
  4. 4Sorting and limiting
  5. 5Aggregates and GROUP BY
  6. 6Joins
  7. 7Keys and relationships
  8. 8Subqueries and CTEs
  9. 9Indexes and performance
  10. 10Changing data

Chapter 9 of 10

Indexes and performance

Why a query is slow.

What an index does

An index is a sorted structure that lets the database find rows without reading the whole table. It speeds up lookups and sorting, and costs storage and time on every write.

  • Index the columns you filter and join on, not every column.
  • Wrapping a column in a function usually stops the index being used.

Reading a plan

EXPLAIN shows the strategy the database chose. A sequential scan of a large table where you expected an index lookup is the usual explanation for a slow query.