====== Index Tuning ======

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

===== What is an Index? =====

An index works like a book index.

Without index:

<code> Need to scan every row </code>

With index:

<code> Jump directly to matching rows </code>

===== Example Without Index =====

Table:

<code sql> users ------------------- id email name </code>

Query:

<code sql> SELECT * FROM users WHERE email = 'a@gmail.com'; </code>

Without index:

<code> MySQL scans ALL rows </code>

This is called:

<code> Full Table Scan </code>

Slow when table becomes large.

===== Example With Index =====

Create index:

<code sql> CREATE INDEX idx_users_email ON users(email); </code>

Now MySQL can:

<code> directly locate matching rows </code>

instead of scanning entire table.

=> much faster

====== B-Tree Index ======

===== What is it? =====

Default MySQL index structure.

Used for:

equality search
range search
sorting
prefix matching

===== Example =====

<code sql> CREATE INDEX idx_users_email ON users(email); </code>

Fast queries:

<code sql> WHERE email = 'a@gmail.com' WHERE email > 'm@gmail.com' ORDER BY email </code>

===== Internally =====

B-Tree stores data in sorted order:

<code> A C F H M Z </code>

MySQL can quickly navigate tree branches.

Complexity:

<code> O(log n) </code>

instead of:

<code> O(n) </code>

====== Composite Index ======

===== What is it? =====

Index with multiple columns.

Example:

<code sql> CREATE INDEX idx_orders_user_status ON orders(user_id, status); </code>

===== Why Useful? =====

Query:

<code sql> SELECT * FROM orders WHERE user_id = 10 AND status = 'PAID'; </code>

Without composite index:

<code> scan many rows </code>

With composite index:

<code> direct lookup </code>

===== Leftmost Prefix Rule =====

Composite index:

<code sql> (user_id, status, created_at) </code>

Works for:

<code sql> WHERE user_id = ? WHERE user_id = ? AND status = ? WHERE user_id = ? AND status = ? AND created_at > ? </code>

Does NOT work efficiently for:

<code sql> WHERE status = 'PAID' </code>

because user_id is missing.

====== FULLTEXT Index ======

===== What is it? =====

Used for text searching.

Example:

<code sql> CREATE FULLTEXT INDEX idx_articles_content ON articles(content); </code>

===== Example Query =====

<code sql> SELECT * FROM articles WHERE MATCH(content) AGAINST('mysql optimization'); </code>

Useful for:

search engine
blog search
article search

===== Difference from LIKE =====

Using LIKE:

<code sql> WHERE content LIKE '%mysql%' </code>

Usually causes:

<code> full table scan </code>

FULLTEXT is much faster for large text datasets.

====== EXPLAIN ======

===== What is it? =====

Shows how MySQL executes query.

Most important tool for index tuning.

===== Example =====

<code sql> EXPLAIN SELECT * FROM users WHERE email = 'a@gmail.com'; </code>

===== Important Fields =====

^ Field ^ Meaning ^
| type | query efficiency |
| key | index being used |
| rows | estimated scanned rows |
| Extra | additional operations |

===== Good Query =====

<code> type = ref rows = 1 key = idx_users_email </code>

Meaning:

using index
scanning very few rows

===== Bad Query =====

<code> type = ALL key = NULL rows = 1000000 </code>

Meaning:

full table scan
no index used

Very slow.

====== Common Index Problems ======

===== Too Many Indexes =====

Every INSERT/UPDATE/DELETE must also update indexes.

Too many indexes cause:

slower writes
more disk usage
higher memory usage

===== Index Not Used =====

Example:

<code sql> WHERE YEAR(created_at) = 2025 </code>

Problem:

<code> function on indexed column </code>

MySQL may ignore index.

Better:

<code sql> WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01' </code>

===== Leading Wildcard =====

Bad:

<code sql> WHERE email LIKE '%gmail.com' </code>

Cannot efficiently use B-Tree index.

Better:

<code sql> WHERE email LIKE 'abc%' </code>

====== Query Optimization Tips ======

===== Select Only Needed Columns =====

Bad:

<code sql> SELECT * FROM users; </code>

Better:

<code sql> SELECT id, name FROM users; </code>

===== Avoid N+1 Queries =====

Bad:

<code> 1 query for users 1000 queries for orders </code>

Better:

<code sql> JOIN users and orders </code>

===== Use Proper Index =====

Common indexing strategy:

^ Query Pattern ^ Recommended Index ^
| WHERE email = ? | (email) |
| WHERE user_id = ? AND status = ? | (user_id, status) |
| ORDER BY created_at | (created_at) |
| WHERE created_at BETWEEN | (created_at) |

====== Production Example ======

Table:

<code sql> orders ( id, user_id, status, created_at ) </code>

Common query:

<code sql> SELECT * FROM orders WHERE user_id = 100 AND status = 'PAID' ORDER BY created_at DESC LIMIT 20; </code>

Recommended index:

<code sql> CREATE INDEX idx_orders_user_status_created ON orders(user_id, status, created_at); </code>

Why:

filter by user_id
filter by status
sort by created_at

=> avoids full scan and extra sorting.

====== Summary ======

^ Concept ^ Purpose ^
| B-Tree Index | Fast lookup and sorting |
| Composite Index | Optimize multi-column queries |
| FULLTEXT Index | Fast text searching |
| EXPLAIN | Analyze query execution |
| Index Tuning | Improve query performance |