Learn › MySQL › Rahul speeds up the app with indexes

MySQL Ch 5 / 18 Intermediate

⚡

Rahul speeds up the app with indexes

CREATE INDEX, EXPLAIN, slow query log — finding and fixing slow queries

⏱ 13 min 6 commands 5 takeaways

⚡

In this chapter

Rahul

Backend developer, queries going slow

The story

Rahul's app had 500,000 users and 2 million orders. Queries that took 5ms now took 8 seconds. His senior said: you need indexes.

Rahul asked what an index is. His senior said: imagine a 500-page book with no table of contents. Finding the word Bangalore means reading every page. An index is the table of contents -- it tells MySQL exactly where to look.

WHAT HAPPENS WITHOUT AN INDEX

SELECT * FROM users WHERE city = 'Mumbai';

MySQL scans every row:
Row 1: city='Hyderabad'? No. Skip.
Row 2: city='Mumbai'? Yes. Keep.
Row 3: city='Bangalore'? No. Skip.
... repeat 500,000 times

This is a full table scan. With 500,000 rows it takes seconds.

CREATING AN INDEX

CREATE INDEX idx_city ON users(city);

Now MySQL looks up 'Mumbai' in the index, finds exactly which rows match, and jumps directly there. Milliseconds instead of seconds.

-- Index on foreign key (essential for JOINs):
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Composite index for queries filtering two columns together:
CREATE INDEX idx_city_created ON users(city, created_at);

-- See all indexes:
SHOW INDEX FROM users;

-- Remove an index:
DROP INDEX idx_city ON users;

EXPLAIN -- THE MOST IMPORTANT DEBUGGING TOOL

EXPLAIN shows you exactly how MySQL runs a query -- whether it uses an index or scans the entire table.

EXPLAIN SELECT * FROM users WHERE city = 'Mumbai';

Before index:

type: ALL    rows: 495820    Extra: Using where
-- type=ALL means full table scan (BAD)

After adding index on city:

type: ref    key: idx_city    rows: 23
-- type=ref means index lookup (GOOD)
-- rows=23 means MySQL only examined 23 rows

Good types (fast): const, eq_ref, ref, range

Bad types (slow): ALL, index (full scan)

ALWAYS run EXPLAIN before celebrating a working query.

WHEN TO ADD AN INDEX

Add an index when a column:

- Appears in WHERE clauses frequently

- Appears in ORDER BY frequently

- Is used in JOINs (foreign keys almost always need indexes)

- Is used in GROUP BY

Do NOT index every column. Indexes speed up SELECT but slow down INSERT, UPDATE, and DELETE because MySQL updates the index too.

FINDING SLOW QUERIES

-- Enable slow query log:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

tail -f /var/log/mysql/slow.log

-- Top slowest queries via performance schema:
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1000000000000 AS avg_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;

Rahul added three indexes. His 8-second query dropped to 12 milliseconds. His senior said: this is why experienced engineers check EXPLAIN before celebrating a working query.

Key takeaways

EXPLAIN before any SELECT tells you if MySQL does a full table scan (type=ALL is bad) or uses an index (type=ref is good)

A single index on a heavily scanned column can drop a query from 8 seconds to 12 milliseconds

Foreign key columns like user_id almost always need indexes — JOIN without an index causes a full scan of the joined table

Composite index (col1, col2) helps queries that filter on both columns — column order in the index matters

Too many indexes slow down INSERT, UPDATE, and DELETE — only index what appears in WHERE, JOIN, or ORDER BY

Commands from this chapter

$ EXPLAIN SELECT * FROM users WHERE city = 'Mumbai';

See if MySQL uses an index or does a full table scan

$ CREATE INDEX idx_city ON users(city);

Add an index on the city column

$ CREATE INDEX idx_orders_user_id ON orders(user_id);

Index the foreign key — essential for JOIN performance

$ SHOW INDEX FROM users;

See all indexes on a table

$ SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1;

Enable logging of slow queries

$ DROP INDEX idx_city ON users;

Remove an index