Database
Understanding MySQL Indexes Through Everyday Queries
Learn when indexes help searches and how to inspect queries with EXPLAIN.
You are reading a translated version.
Start with access patterns
Indexes help a database locate rows without always reading an entire table. Consider an articles table frequently searched by slug.
CREATE TABLE articles (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
slug VARCHAR(220) NOT NULL,
title VARCHAR(200) NOT NULL,
UNIQUE KEY articles_slug_unique (slug)
);
EXPLAIN SELECT title FROM articles
WHERE slug = 'belajar-indeks';
Read the execution plan
The key column in EXPLAIN output identifies the chosen index. The rows value estimates the number of rows examined; it is not an execution-time measurement.
Writes have a cost
Every index requires storage and maintenance when data changes. Add indexes based on frequently executed queries, then measure their impact using data representative of real usage.
Exercise
Populate the table with several articles and compare a query filtering by slug with one filtering by title. Observe which indexes appear in their execution plans.