Lesson 25 of 30
83%
Indexes
An index is a shortcut MySQL uses to find rows faster.
The primary key already has one. Extra indexes help other columns you search a lot.
Think of the index at the back of a book.
Create an index
sql
Result
Query OK, 0 rows affected
| Table | Key_name | Column_name |
|---|---|---|
| students | idx_students_city | city |
1 row
When they help
1
WHERE on that column
2
JOIN on that column
3
ORDER BY on that column
When they hurt
Each INSERT and UPDATE must also update the index.
Do not index every column. Index the ones you filter and join.
See indexes
sql
Result
| Table | Non_unique | Key_name | Column_name |
|---|---|---|---|
| students | 0 | PRIMARY | id |
| students | 1 | idx_students_city | city |
2 rows
Drop one
sql
Result
Query OK, 0 rows affected
Tip: Foreign key columns almost always deserve an index.
Test yourself
Three quick questions made just for this lesson. Earn 10 XP per correct answer.