MySQL·MySQL from first query to production

Indexes

Design indexes for the queries you actually run and understand the cost.

Beginner 15 min read Lesson 5 of 300 Challenge included

What you will be able to do

Index foreign keys used in joins.
Use composite indexes in the order of your filters.
Avoid indexing low-cardinality columns alone.
Remove indexes that no query uses.

In plain English

An index is a sorted structure that lets MySQL find rows without scanning the table. Index the columns in your WHERE, JOIN and ORDER BY clauses. Every index speeds reads but costs write time and storage.

index.sqlSQL

CREATE INDEX lessons_course_minutes_idx
ON lessons (course_slug, minutes);

EXPLAIN SELECT title
FROM lessons
WHERE course_slug = 'css' AND minutes >= 15
ORDER BY minutes DESC;

Your turn

Create a composite index

Easy

Create an index on lessons(course_slug, minutes).

SQL · starter

CREATE INDEX

Check your understanding before moving on.

Knowledge checkQuestion 1

What is the main cost of adding an index?

Select an answer to continue

Study tools

Make it yours.

Keep a private note for this lesson, then turn the key ideas into flashcards you can revisit.

Sign in to save notes and flashcards.Both are private to your account and stay with your progress.

Finished reading?

Lock in what you just learned.

Mark this lesson complete to save your place and move one step closer to the certificate.

Next: Transactions

Lesson 5 of 300 · 0% of course