Skip to content

Indexes & Performance

Speed up queries with indexes and learn to use EXPLAIN.

Without indexes, MySQL must scan every row to find matches (a full table scan). Indexes are data structures that allow MySQL to find rows without scanning the entire table. They speed up reads but slow down writes.

Definition

A database index is a data structure that speeds up row retrieval at the cost of slower writes. Indexes work like a book index: instead of reading every page, the database jumps directly to the matching rows.

Required step: If you do not have these tables yet, run this setup in the playground before continuing. It creates the tables used by every example in this stage.

CREATE DATABASE IF NOT EXISTS school;
USE school;

CREATE TABLE IF NOT EXISTS students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE IF NOT EXISTS enrollments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  course_name VARCHAR(100) NOT NULL,
  score DECIMAL(5,2),
  FOREIGN KEY (student_id) REFERENCES students(id)
);

CREATE TABLE IF NOT EXISTS courses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(10) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL
);

INSERT INTO products (name, description, price) VALUES
  ('Wireless Keyboard', 'Ergonomic wireless keyboard with quiet keys', 29.99),
  ('Bluetooth Mouse', 'Compact bluetooth mouse for laptops', 19.99),
  ('USB Hub', '4-port USB 3.0 hub for fast data transfer', 24.99);

Creating Indexes

Use CREATE INDEX to add an index on columns you frequently search, join, or sort by.

Single-column index

CREATE INDEX idx_student_name ON students (name);

This index speeds up queries like WHERE name = 'Alice' or ORDER BY name. MySQL can find the row without scanning the entire table.

Composite (multi-column) index

CREATE INDEX idx_enrollment ON enrollments (student_id, course_name);

Column order matters. This index helps WHERE student_id = 1 AND course_name = 'Math', and also WHERE student_id = 1 alone. It cannot be used for lookups on course_name alone. However, if the query only needs columns in the index (a covering index), MySQL can scan the index instead of the table data.

UNIQUE index

CREATE UNIQUE INDEX idx_course_code ON courses (code);

A UNIQUE index enforces uniqueness and speeds up lookups. It is like adding UNIQUE constraint and an index at the same time.

Drop an index

DROP INDEX idx_student_name ON students;

Removing an index frees disk space and speeds up writes, but slows down reads on that column.

FULLTEXT Indexes

FULLTEXT indexes enable efficient text searching across large text columns. They support natural language searches and boolean mode with operators.

Text searching:FULLTEXT search

FULLTEXT indexes work on CHAR, VARCHAR, and TEXT columns. Unlike LIKE '%term%', FULLTEXT searches are fast and scale well.

  • Natural language mode: default, ranks results by relevance
  • Boolean mode: supports operators: + (must include), - (must exclude), * (wildcard), " (exact phrase)

Create a FULLTEXT index and search

CREATE FULLTEXT INDEX idx_desc ON products (description);

-- Natural language search
SELECT name, MATCH(description) AGAINST('wireless keyboard') AS relevance
FROM products
WHERE MATCH(description) AGAINST('wireless keyboard')
ORDER BY relevance DESC;

MATCH ... AGAINST searches the indexed column. In natural language mode, results are ranked by relevance score. Only rows with at least one match are returned.

Boolean mode search

-- Must include 'wireless', must NOT include 'bluetooth'
SELECT name FROM products
WHERE MATCH(description) AGAINST('+wireless -bluetooth' IN BOOLEAN MODE);

-- Exact phrase match
SELECT name FROM products
WHERE MATCH(description) AGAINST('"ergonomic design"' IN BOOLEAN MODE);

In boolean mode, + means the term must be present, - means it must be absent, and quotes match an exact phrase. You can also use * as a wildcard (e.g., 'key*' matches 'keyboard', 'keypad').

EXPLAIN

EXPLAIN shows how MySQL executes a query. It reveals which indexes are used and how many rows are scanned.

Basic EXPLAIN

EXPLAIN SELECT * FROM students WHERE name = 'Alice';

Look at the 'type' column. 'const' or 'ref' means an index is used (good). 'ALL' means a full table scan (bad; add an index).

EXPLAIN with JOIN

EXPLAIN SELECT s.name, e.course_name
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id;

Check the 'key' column to see which indexes are used. Check 'rows' to see how many rows MySQL estimates it will scan.

Reading the output:EXPLAIN Access Types

  • const: best: single row lookup (primary key or unique index)
  • eq_ref: best for joins: one row from primary key or unique index per combination
  • ref: good: index lookup with multiple matches
  • range: acceptable: index scan over a range
  • index: okay: full index scan (covers the query or narrower than the table)
  • ALL: worst: full table scan (no index used)

EXPLAIN ANALYZE

EXPLAIN ANALYZE actually runs the query and shows actual execution times alongside the optimizer's estimates. Use it to see where time is really spent.

EXPLAIN ANALYZE vs plain EXPLAIN

EXPLAIN ANALYZE
SELECT s.name, e.course_name
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
WHERE e.score > 80;

EXPLAIN ANALYZE executes the query and adds an 'actual time' column showing real milliseconds per step. Compare 'estimated rows' vs 'actual rows' to spot optimizer misestimates. The query runs once. The results are returned, not just the plan.

When to Index

1.Index these columns

  • Columns in WHERE clauses
  • Columns used in JOIN ON conditions
  • Columns used in ORDER BY
  • Columns used in GROUP BY

2.Avoid over-indexing

  • Each index slows down INSERT, UPDATE, DELETE
  • Indexes consume disk space
  • For small tables, a full scan may be faster than an index
  • Leading wildcards (LIKE '%term%') prevent index usage

What does EXPLAIN type: ALL mean?

You create INDEX idx ON orders (customer_id, order_date). Which query can use it?

Key Takeaways

  • Indexes speed up reads (SELECT) but slow down writes (INSERT/UPDATE/DELETE).
  • Use EXPLAIN before optimizing any slow query.
  • Composite indexes are ordered. The leftmost column must be used.
  • type: ALL means a full table scan; aim for ref, range, or const.
  • Avoid over-indexing; each index has a cost.

Ready to test your knowledge?

Take a Quiz

Related Tutorials