Index Tuning is the process of:

creating the right indexes removing unnecessary indexes optimizing query performance

Goal:

make queries faster reduce full table scans reduce CPU and disk I/O improve application response time

An index works like a book index.

Without index:

 Need to scan every row 

With index:

 Jump directly to matching rows 

Table:

 users ------------------- id email name 

Query:

 SELECT * FROM users WHERE email = 'a@gmail.com'; 

Without index:

 MySQL scans ALL rows 

This is called:

 Full Table Scan 

Slow when table becomes large.

Create index:

 CREATE INDEX idx_users_email ON users(email); 

Now MySQL can:

 directly locate matching rows 

instead of scanning entire table.

⇒ much faster

Default MySQL index structure.

Used for:

equality search range search sorting prefix matching

 CREATE INDEX idx_users_email ON users(email); 

Fast queries:

 WHERE email = 'a@gmail.com' WHERE email > 'm@gmail.com' ORDER BY email 

B-Tree stores data in sorted order:

 A C F H M Z 

MySQL can quickly navigate tree branches.

Complexity:

 O(log n) 

instead of:

 O(n) 

Index with multiple columns.

Example:

 CREATE INDEX idx_orders_user_status ON orders(user_id, STATUS); 

Query:

 SELECT * FROM orders WHERE user_id = 10 AND STATUS = 'PAID'; 

Without composite index:

 scan many rows 

With composite index:

 direct lookup 

Composite index:

 (user_id, STATUS, created_at) 

Works for:

 WHERE user_id = ? WHERE user_id = ? AND STATUS = ? WHERE user_id = ? AND STATUS = ? AND created_at > ? 

Does NOT work efficiently for:

 WHERE STATUS = 'PAID' 

because user_id is missing.

Used for text searching.

Example:

 CREATE FULLTEXT INDEX idx_articles_content ON articles(content); 

 SELECT * FROM articles WHERE MATCH(content) AGAINST('mysql optimization'); 

Useful for:

search engine blog search article search

Using LIKE:

 WHERE content LIKE '%mysql%' 

Usually causes:

 full table scan 

FULLTEXT is much faster for large text datasets.

Shows how MySQL executes query.

Most important tool for index tuning.

 EXPLAIN SELECT * FROM users WHERE email = 'a@gmail.com'; 

 type = ref rows = 1 key = idx_users_email 

Meaning:

using index scanning very few rows

 type = ALL key = NULL rows = 1000000 

Meaning:

full table scan no index used

Very slow.

Every INSERT/UPDATE/DELETE must also update indexes.

Too many indexes cause:

slower writes more disk usage higher memory usage

Example:

 WHERE YEAR(created_at) = 2025 

Problem:

 function on indexed column 

MySQL may ignore index.

Better:

 WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01' 

Bad:

 WHERE email LIKE '%gmail.com' 

Cannot efficiently use B-Tree index.

Better:

 WHERE email LIKE 'abc%' 

Bad:

 SELECT * FROM users; 

Better:

 SELECT id, name FROM users; 

Bad:

 1 query for users 1000 queries for orders 

Better:

 JOIN users AND orders 

Common indexing strategy:

Table:

 orders ( id, user_id, STATUS, created_at ) 

Common query:

 SELECT * FROM orders WHERE user_id = 100 AND STATUS = 'PAID' ORDER BY created_at DESC LIMIT 20; 

Recommended index:

 CREATE INDEX idx_orders_user_status_created ON orders(user_id, STATUS, created_at); 

Why:

filter by user_id filter by status sort by created_at

⇒ avoids full scan and extra sorting.