SQL Indexes — Complete Notes (MySQL Edition)

Clustered · Non-Clustered · Column Store · Unique · Filtered · Maintenance · Strategy

theme: study · May 31, 2026

[TOC]

Part 1: Introduction to Indexes

What Is an Index?
An index is a data structure that provides quick access to rows in a table to improve the speed of your
queries. Think of it like the index at the back of a book — instead of flipping through every page to find a
topic, you jump straight to the right page.

Another way to think about it: imagine a large hotel with no room-numbering guide. You'd have to go floor
by floor checking every room. But with a map and signage (the index), you jump directly to the right room.
A database index does exactly that — it helps the database locate data without scanning everything.

■ Note

Key Idea — Indexes are all about tradeoffs. They speed up reads but slow down writes (INSERT, UPDATE,
DELETE), because every write operation must also update the index structures.

Types of Indexes (Overview)

Indexes can be categorized in three ways:

By Structure — How the database organizes and references data:

• Clustered Index
• Non-Clustered Index

By Storage — How data is physically stored:

• Row Store Index

• Column Store Index

By Function — Special behavioral rules:

• Unique Index
• Filtered Index

Part 2: How Data Is Stored — Pages and the Heap

Data Pages

SQL Indexes — Complete Notes (MySQL Edition) Page 1

SQL Indexes — Complete Notes (MySQL Edition)

Behind the scenes, MySQL (and SQL databases in general) does not store data as visible rows and
columns — it stores data in data files made up of fixed-size units called pages.

A page is the unit of data storage. Each page has three main sections:

• Page Header — metadata about the page (page ID, etc.)

• Row Data Area — where the actual rows of your table are stored; MySQL fits as many rows as
possible in one page
• Offset Array — a quick lookup table tracking where each row begins within the page, so the database
can locate a row without scanning the whole page

The Heap Structure (No Index)

When you create a table in MySQL without any index, data is stored in a heap — rows are inserted in
whatever order they arrive, with no sorting. This is called a heap table.

-- Creating a table in MySQL with no index = heap structure

CREATE TABLE temp_customers (
customer_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
country VARCHAR(50),
score INT
);

-- MySQL stores rows in insertion order - no sorting, no organization

INSERT INTO temp_customers VALUES (12, 'Alice', 'Smith', 'USA', 750);
INSERT INTO temp_customers VALUES (5, 'Bob', 'Jones', 'UK', 400);
INSERT INTO temp_customers VALUES (15, 'Carol', 'Brown', 'USA', 900);

Tradeoff:

• ■ Very fast INSERT operations (just append the row)

• ■ Very slow SELECT operations — MySQL must scan every single row (Full Table Scan)

■■ Warning

Full Table Scan — When MySQL has no index to use, it performs a Full Table Scan — it reads every page,
every row, until it finds what it needs. For tables with millions of rows this is extremely slow.

Part 3: Clustered Index

What Is a Clustered Index?

Page 2
SQL Indexes — Complete Notes (MySQL Edition)

A clustered index physically sorts and stores the table rows based on the indexed column(s). In MySQL
with InnoDB, every table has a clustered index — by default it is the Primary Key.

When you define a PRIMARY KEY in MySQL, InnoDB automatically creates a clustered index on that
column. The rows in the table are physically arranged according to the primary key order.

How the B-Tree Is Built (Clustered Index)

When you create a clustered index, the database:

• Physically sorts all data pages based on the indexed column

• Builds a B-Tree (Balanced Tree) structure on top:
• Leaf nodes contain the actual data pages (the real rows)
• Intermediate nodes contain index pages (pointers to other index pages or data pages)
• Root node is the single top-level index page

direction: TB
Root Node (Index Page) -> Intermediate Node Left
Root Node (Index Page) -> Intermediate Node Right
Intermediate Node Left -> Leaf Data Page 1
Intermediate Node Left -> Leaf Data Page 2
Intermediate Node Right -> Leaf Data Page 3
Intermediate Node Right -> Leaf Data Page 4

Finding a row using the clustered index:

MySQL starts at the root, follows pointers down to the correct intermediate node, then to the correct leaf
data page — all in just a few jumps instead of a full scan.

Clustered Index in MySQL (InnoDB)

-- In MySQL InnoDB, defining PRIMARY KEY automatically creates the clustered index
CREATE TABLE customers (
customer_id INT NOT NULL,
first_name VARCHAR(50),
last_name VARCHAR(50),
country VARCHAR(50),
score INT,
PRIMARY KEY (customer_id) -- clustered index created automatically
);

You can also explicitly define a primary key on an existing table:

ALTER TABLE customers

ADD PRIMARY KEY (customer_id);

Page 3
SQL Indexes — Complete Notes (MySQL Edition)

If you want to see the indexes on a table:

-- Show all indexes on a table

SHOW INDEX FROM customers;

-- Or use INFORMATION_SCHEMA
SELECT index_name, index_type, column_name, non_unique
FROM information_schema.statistics
WHERE table_schema = 'your_database'
AND table_name = 'customers'
ORDER BY index_name, seq_in_index;

Rules for Clustered Index

• In MySQL InnoDB, you can have only one clustered index per table (this makes physical sense —
data can only be sorted one way at a time)
• The clustered index is always the Primary Key in InnoDB
• If no PRIMARY KEY is defined, InnoDB looks for the first UNIQUE NOT NULL column and uses it; if
none exists, InnoDB internally generates a hidden row ID as the clustered index

Good Candidates for Clustered Index (Primary Key)

A good clustered index column should be:

• Unique — no duplicate values

• Stable / not frequently updated — because updating the clustered column forces InnoDB to
physically reorganize the data
• Monotonically increasing (e.g., AUTO_INCREMENT integer) — prevents page splits

-- Ideal: auto-increment integer primary key

CREATE TABLE orders (
order_id INT NOT NULL AUTO_INCREMENT,
order_date DATE,
customer_id INT,
amount DECIMAL(10,2),
PRIMARY KEY (order_id)
);

■ Tip

Best Practice — Always define an AUTO_INCREMENT integer PRIMARY KEY in InnoDB tables. This gives
you the best clustered index performance: unique, never updated, always increasing.

Clustered Index — Read vs Write Performance

Page 4
SQL Indexes — Complete Notes (MySQL Edition)

Operation Performance

SELECT with PK lookup Very fast — B-Tree navigation

Range queries (BETWEEN, >, <) Fast — data is physically sorted

INSERT new row Slightly slower than heap (must maintain sort order)

UPDATE on indexed column Expensive — row may need to be moved

Part 4: Non-Clustered Index (Secondary Index)

What Is a Non-Clustered Index?

A non-clustered index is a separate data structure that exists alongside your table. It does not physically
reorder the table rows — it creates an independent B-Tree that contains:

• The indexed column value (the key)

• A row locator pointer back to the actual row

In MySQL InnoDB, the row locator in a non-clustered (secondary) index is the Primary Key value of the
row (not a physical file offset). When MySQL follows a secondary index to find a row, it:

• Navigates the secondary index B-Tree to find the primary key value
• Then navigates the clustered index B-Tree using that primary key to retrieve the full row

This second lookup step is called a clustered index lookup (sometimes called a "double lookup").

How the B-Tree Is Built (Non-Clustered Index)

direction: TB
Root Node (Index Page) -> Intermediate Index Page Left
Root Node (Index Page) -> Intermediate Index Page Right
Intermediate Index Page Left -> Leaf Index Page 1 (key + PK pointer)
Intermediate Index Page Left -> Leaf Index Page 2 (key + PK pointer)
Intermediate Index Page Right -> Leaf Index Page 3 (key + PK pointer)
Intermediate Index Page Right -> Leaf Index Page 4 (key + PK pointer)

The leaf nodes contain only the indexed column value + primary key — not the full row data. To get the full
row, MySQL uses the primary key to look it up in the clustered index.

Creating Non-Clustered Indexes in MySQL

-- Basic non-clustered (secondary) index on last_name

Page 5
SQL Indexes — Complete Notes (MySQL Edition)

CREATE INDEX idx_customers_last_name

ON customers (last_name);

-- Index on a single column used in WHERE clause

CREATE INDEX idx_customers_country
ON customers (country);

-- You can create multiple secondary indexes on the same table

CREATE INDEX idx_customers_first_name
ON customers (first_name);

To drop an index:

DROP INDEX idx_customers_last_name ON customers;

Composite Index (Multi-Column Index)

A composite index covers multiple columns. It is used when your WHERE clause or JOIN condition
involves more than one column.

-- Composite index on country and score

CREATE INDEX idx_customers_country_score
ON customers (country, score);

-- Query that uses this index efficiently

SELECT * FROM customers
WHERE country = 'USA'
AND score > 500;

■■ Warning

Column Order Is Critical in Composite Indexes — MySQL can only use a composite index from the leftmost
column forward. This is called the Leftmost Prefix Rule.

Leftmost Prefix Rule — Detailed

Given an index on (A, B, C, D):

Query uses... Index used?

WHERE A = ... ■ Yes

WHERE A = ... AND B = ... ■ Yes

WHERE A = ... AND B = ... AND C = ... ■ Yes

WHERE B = ... (skips A) ■ No

WHERE A = ... AND C = ... (skips B) ■ No (index used only for A)

Page 6
SQL Indexes — Complete Notes (MySQL Edition)

Query uses... Index used?

WHERE C = ... AND D = ... (skips A and B) ■ No

-- This query USES the composite index (starts from leftmost column)
SELECT * FROM customers
WHERE country = 'USA'; -- leftmost column included ■

-- This query does NOT use the composite index

SELECT * FROM customers
WHERE score > 500; -- skips 'country' which is leftmost ■

Always define the composite index columns in the same order they appear in your query's WHERE
clause.

Non-Clustered vs Clustered — Side-by-Side

Feature Clustered Index Non-Clustered Index

Number allowed per table 1 (Primary Key) Many

Leaf nodes contain Actual row data Key value + PK pointer

Physically sorts table rows? Yes No

Read performance Faster (one lookup) Slightly slower (two lookups)

Write performance Slower (maintain sort) Faster

Storage overhead Less (no extra structure) More (extra B-Tree)

In MySQL InnoDB Always the Primary Key Any other column(s)

Part 5: Row Store vs Column Store Index

Row Store Index (Default)

In a row store index, data is organized and stored row by row. All column values for a single row are
stored together in a data page. This is the traditional method — it is what all regular InnoDB tables use.

Data Page (Row Store):

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
■ Row 1: [id=1, name='Alice', status='active'] ■
■ Row 2: [id=2, name='Bob', status='inactive'] ■

Page 7
SQL Indexes — Complete Notes (MySQL Edition)

■ Row 3: [id=3, name='Carol', status='active'] ■

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

Best for: OLTP systems (Online Transaction Processing) — e-commerce, banking, ERP — where you
frequently access full records and have many INSERT/UPDATE/DELETE operations.

Column Store Index

In a column store index, data is organized and stored column by column. All values of one column are
stored together in a single data page, completely separate from other columns.

Column Store Pages:

■■■■■■■■■■■■■■■■■ ■■■■■■■■■■■■■■■■■■■■■■■■■■■■ ■■■■■■■■■■■■■■■■■■■■■■
■ ID column ■ ■ Name column ■ ■ Status column ■
■ 1, 2, 3, 4, 5■ ■ Alice, Bob, Carol, Dan, E ■ ■ 1, 2, 1, 1, 2 ■
■■■■■■■■■■■■■■■■■ ■■■■■■■■■■■■■■■■■■■■■■■■■■■■ ■■■■■■■■■■■■■■■■■■■■■■
(with dictionary:
1=active, 2=inactive)

How MySQL Builds a Column Store Index

• Row Grouping — Rows are divided into row groups (up to ~1 million rows each), for parallel
processing efficiency
• Column Segmentation — Within each row group, columns are separated into individual segments
• Data Compression — Each column segment is compressed using dictionary encoding:
• A dictionary page maps long repeated string values (e.g., "active", "inactive") to small integer
codes (1, 2)
• A data stream stores only the compressed codes
• LOB Storage — Compressed column segments are stored in special Large Object (LOB) data pages

Why Column Store Is Faster for Analytics

Consider this query:

SELECT COUNT(*) FROM customers WHERE status = 'active';

With Row Store: MySQL reads every data page, pulling all columns (id, name, status) even though only
status is needed. Lots of unnecessary data is read from disk.

With Column Store: MySQL reads only the status column's data page. It reads the compressed data
stream, filters out the entries where code = 2 (inactive), and counts the rest. Far less data is read from
disk.

■ Tip

Key Insight — Column store is fast for analytics because it reads only the columns the query actually needs.
Row store reads entire rows even when most columns are irrelevant.

Page 8
SQL Indexes — Complete Notes (MySQL Edition)

Column Store Index in MySQL

MySQL 8.0+ supports column store via secondary columnstore indexes. Note that MySQL's
implementation differs slightly from SQL Server; here is the correct MySQL syntax:

-- MySQL does not have a native "COLUMNSTORE INDEX" keyword in standard editions.
-- Column store / analytical indexes in MySQL ecosystem are available in:
-- 1. MySQL HeatWave (cloud, uses HeatWave cluster)
-- 2. MariaDB ColumnStore
-- 3. For pure MySQL 8.x: InnoDB does not support column store natively;
-- analytical workloads typically use a separate analytics engine or
-- tools like ClickHouse, Redshift, etc.

-- In MariaDB (MySQL-compatible), the syntax is:

CREATE TABLE fact_sales (
sale_id INT NOT NULL,
order_date DATE,
customer_id INT,
amount DECIMAL(10,2),
PRIMARY KEY (sale_id)
) ENGINE=ColumnStore; -- MariaDB ColumnStore engine

■ Note

MySQL vs SQL Server Column Store — In SQL Server, column store indexes are added on top of existing
InnoDB-style tables with CREATE CLUSTERED COLUMNSTORE INDEX. In standard MySQL 8.0 InnoDB,
column store is not built in — but the concepts are the same. Tools like MySQL HeatWave bring column
store analytics to MySQL. The concepts covered here (column segmentation, dictionary compression, LOB
pages, analytics optimization) apply universally.

Row Store vs Column Store — Comparison

Feature Row Store Column Store

Data organization Row by row Column by column

Storage efficiency More space Less space (compression)

Read performance Balanced Very fast for analytics

Write performance Balanced Slower (complex write path)

I/O efficiency Low (reads all columns) High (reads only needed columns)

Best system type OLTP OLAP

Use case Banking, e-commerce, ERP Data warehouse, reporting, BI

Page 9
SQL Indexes — Complete Notes (MySQL Edition)

Part 6: Unique Index

What Is a Unique Index?

A unique index is an index that also enforces a constraint: no two rows can have the same value in the
indexed column(s). It serves two purposes:

• Data Integrity — prevents duplicate values (e.g., duplicate email addresses, duplicate product codes)
• Query Performance — once MySQL finds a matching row, it can stop searching immediately (since it
is guaranteed there are no more matches)

■ Note

How Unique Index Improves Performance — With a regular index, after finding a match MySQL must
continue scanning in case there are duplicates. With a unique index, MySQL stops at the first match —
slightly faster for exact-value lookups.

Creating a Unique Index in MySQL

-- Create a unique index on the email column

CREATE UNIQUE INDEX idx_customers_email
ON customers (email);

-- Or define uniqueness at table creation

CREATE TABLE users (
user_id INT NOT NULL AUTO_INCREMENT,
email VARCHAR(255) NOT NULL,
username VARCHAR(100) NOT NULL,
PRIMARY KEY (user_id),
UNIQUE KEY uq_users_email (email), -- unique index
UNIQUE KEY uq_users_username (username) -- unique index
);

Testing Data Integrity with a Unique Index

-- This will succeed (new unique email)

INSERT INTO users (email, username) VALUES ('alice@[Link]', 'alice99');

-- This will FAIL with a duplicate key error

INSERT INTO users (email, username) VALUES ('alice@[Link]', 'alice_new');
-- ERROR 1062 (23000): Duplicate entry 'alice@[Link]' for key 'uq_users_email'

■■ Warning

You Cannot Create a Unique Index on a Column That Already Has Duplicates — If you try to add a unique
index to a column that already contains duplicate values, MySQL will reject it with an error. You must clean

Page 10
SQL Indexes — Complete Notes (MySQL Edition)

the data first (remove or deduplicate) before adding the unique constraint.

Tradeoff of Unique Index

Aspect Effect

Write performance Slightly slower — MySQL must verify uniqueness on

every INSERT/UPDATE

Read performance Slightly faster — MySQL stops at first match

Data integrity Enforced at the database level

Part 7: Filtered Index (Partial Index)

What Is a Filtered Index?

A filtered index (called a partial index in MySQL/PostgreSQL terminology) is a regular index that only
includes rows meeting a specific condition. Instead of indexing the entire table, you index only a relevant
subset.

This means:

• The B-Tree is smaller (fewer rows = smaller index)

• Queries targeting that subset are faster
• Less storage is required

■ Note

Filtered Index in MySQL — MySQL 8.0.13+ supports functional indexes and partial indexes using
generated columns, but does not support the direct WHERE clause on CREATE INDEX like SQL Server does.
The standard MySQL approach is to use a generated column with an index, or rely on the optimizer with a
regular index plus WHERE clause.

Approach 1 — Generated Column (Recommended in MySQL)

The cleanest way to create a filtered/partial index in MySQL:

-- Suppose we always query only active customers

-- Step 1: Add a generated column that is NULL for inactive rows
ALTER TABLE customers
ADD COLUMN is_active_filter TINYINT(1)
GENERATED ALWAYS AS (
IF(status = 'active', 1, NULL)
) STORED;

Page 11
SQL Indexes — Complete Notes (MySQL Edition)

-- Step 2: Create an index on the generated column

-- NULL values are not indexed in MySQL, so only active rows are indexed
CREATE INDEX idx_customers_active
ON customers (is_active_filter);

-- Query that benefits from this partial index

SELECT * FROM customers
WHERE is_active_filter = 1;

Approach 2 — Regular Index with WHERE (Conceptual Equivalent)

Even with a regular index, if you always filter the same way, MySQL's optimizer will often use the index
efficiently:

-- Regular index on country

CREATE INDEX idx_customers_country
ON customers (country);

-- Query that uses the index efficiently

SELECT * FROM customers
WHERE country = 'USA';

-- A query filtering Germany will also use the index,

-- but if your project ONLY ever queries USA customers,
-- a partial index approach is more storage-efficient

When to Use a Filtered/Partial Index

• You always query only a subset of data (e.g., only active records, only current year, only specific
region)
• The table is large but the relevant subset is small
• You want to reduce index storage without sacrificing query speed for the common case

Restrictions
• Filtered indexes apply to non-clustered (secondary) indexes only — it makes no sense on a clustered
index (which must cover all rows)
• Cannot be applied to column store indexes
• Can be combined with UNIQUE (a unique partial index)

-- Unique partial index: unique product names only among active products
ALTER TABLE products
ADD COLUMN active_product_name VARCHAR(255)
GENERATED ALWAYS AS (

Page 12
SQL Indexes — Complete Notes (MySQL Edition)

IF(is_active = 1, product_name, NULL)

) STORED;

CREATE UNIQUE INDEX uq_products_active_name

ON products (active_product_name);

Part 8: Choosing the Right Index

Quick Reference Guide

Scenario Recommended Index

Primary key column Clustered index (automatic in InnoDB)

No index needed, fast inserts (staging/temp tables) No index (heap)

Non-primary key column used in WHERE/JOIN Non-clustered (secondary) index

Big table with complex analytical aggregations Column store index

Very large table, storage is a concern Column store (compression)

Column with no duplicates — enforce uniqueness + Unique index

performance

Always querying only a subset of rows Filtered/partial index

WHERE clause uses two or more columns Composite (multi-column) index

Decision Flowchart

direction: TB
New index needed? -> Primary key column?
Primary key column? -> Yes: Clustered index (InnoDB PK)
Primary key column? -> No: Need fast inserts only (temp/staging)?
Need fast inserts only (temp/staging)? -> Yes: No index (heap)
Need fast inserts only (temp/staging)? -> No: Big table with aggregations?
Big table with aggregations? -> Yes: Column store index
Big table with aggregations? -> No: Column is unique?
Column is unique? -> Yes: Unique non-clustered index
Column is unique? -> No: Always query a subset?
Always query a subset? -> Yes: Filtered (partial) index
Always query a subset? -> No: Secondary (non-clustered) index

Page 13
SQL Indexes — Complete Notes (MySQL Edition)

Summary by Index Type

Heap (No Index)

• Use for staging tables, temporary tables, bulk load targets

• Fastest possible INSERT performance; terrible SELECT performance

Clustered Index

• Default for every InnoDB primary key

• Use it on: primary keys, auto-increment IDs, date columns where range queries are common
• One per table, never on frequently-updated columns

Non-Clustered (Secondary) Index

• Use on non-PK columns that appear in WHERE, JOIN ON, or ORDER BY

• Foreign key columns are excellent candidates
• Multiple allowed per table

Column Store Index

• Use for OLAP / data warehouse / BI workloads

• Dramatically faster for aggregations (SUM, COUNT, AVG) on large tables
• Also great for reducing storage via compression

Filtered (Partial) Index

• Use when your queries always target a specific subset of rows

• Reduces index size and speeds up queries for that subset

Unique Index

• Use to enforce data integrity (email, username, product code)

• Slightly improves query performance for exact-match lookups

Part 9: Index Maintenance

Why Indexes Need Maintenance

Creating indexes is not a one-time job. Over time, indexes degrade in several ways:

• Fragmentation — as rows are inserted, updated, deleted, data pages become disordered or partially
empty
• Outdated statistics — the database's internal statistics about data distribution get stale, causing the
optimizer to make bad decisions
• Unused indexes — indexes that no query ever uses still consume storage and slow down writes
• Duplicate indexes — multiple indexes covering the same column(s)

Page 14
SQL Indexes — Complete Notes (MySQL Edition)

• Missing indexes — columns that are frequently queried but not indexed

Task 1 — Monitor Index Usage

Before doing anything else, check whether your existing indexes are actually being used.

In MySQL, index usage is tracked in performance_schema:

-- Check which indexes are used (requires performance_schema enabled)

SELECT object_schema,
object_name AS table_name,
index_name,
count_star AS total_usage,
count_read,
count_write,
count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database'
ORDER BY count_star ASC; -- sort ascending to see unused ones first

Indexes with count_star = 0 (or NULL) are candidates for removal.

Why drop unused indexes?

• Unused indexes still take up storage

• Every INSERT/UPDATE/DELETE must update all indexes on the table, even if never read
• Fewer indexes = faster writes

■ Tip

Expert Tip — Checking index usage is one of the most impactful things you can do when joining a new
project. In practice, a large percentage of indexes in real projects are never used. Dropping them frees
storage and improves write performance immediately.

Task 2 — Find Missing Indexes

MySQL's optimizer notes when a query would benefit from an index that does not exist. You can query this
from sys schema:

-- Find tables/queries that might benefit from new indexes

SELECT *
FROM sys.schema_unused_indexes;

-- Or check for full table scans (often indicates missing index)

SELECT digest_text,
count_star,
sum_rows_examined,
sum_rows_sent

Page 15
SQL Indexes — Complete Notes (MySQL Edition)

FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_rows_examined DESC
LIMIT 20;

Also check EXPLAIN on slow queries:

-- EXPLAIN shows the execution plan; look for "type=ALL" which means full table scan
EXPLAIN
SELECT * FROM customers
WHERE country = 'USA'
AND score > 500;

If type = ALL appears in the EXPLAIN output, no index is being used for that table — a missing index is
likely the cause.

■■ Warning

Do Not Blindly Create Every Suggested Index — Database recommendations for missing indexes are hints,
not orders. Always evaluate: Is this query run frequently? Is the performance gain worth the write overhead?
Think before you create.

Task 3 — Find Duplicate Indexes

Duplicate indexes occur when multiple indexes cover the same column(s). They waste storage and slow
writes.

-- Find potentially duplicate indexes in MySQL

SELECT t.table_schema,
t.table_name,
GROUP_CONCAT(DISTINCT t.index_name ORDER BY t.index_name) AS index_names,
GROUP_CONCAT(t.column_name ORDER BY t.seq_in_index) AS columns,
COUNT(*) AS index_count
FROM information_schema.statistics t
WHERE t.table_schema = 'your_database'
GROUP BY t.table_schema, t.table_name,
GROUP_CONCAT(t.column_name ORDER BY t.seq_in_index)
HAVING COUNT(*) > 1
ORDER BY t.table_name;

When you find duplicates, decide which one to keep (usually the more specific or more used one) and
drop the rest.

Task 4 — Update Statistics

MySQL uses statistics about data distribution to choose the best execution plan. If statistics are outdated,
the optimizer may make poor decisions (e.g., doing a full table scan instead of using an index).

Page 16
SQL Indexes — Complete Notes (MySQL Edition)

-- Update statistics for a specific table

ANALYZE TABLE customers;

-- Update statistics for multiple tables

ANALYZE TABLE customers, orders, products;

MySQL also has innodb_stats_auto_recalc (default: ON) which automatically updates statistics when
about 10% of rows change. However, for large tables or after bulk data loads, run ANALYZE TABLE
manually.

-- After a large data migration, always run ANALYZE

ANALYZE TABLE fact_sales;

■ Tip

Scheduling Statistics Updates — For production databases, schedule a weekly ANALYZE TABLE job (or full
database statistics update) during off-peak hours. Also run it immediately after any large bulk data insert or
data migration.

Task 5 — Monitor and Fix Index Fragmentation

As rows are inserted, updated, and deleted, InnoDB data pages can become fragmented — partially
empty or disordered — leading to wasted space and slower queries.

Check Fragmentation

-- Check table fragmentation (data_free = unused space in pages)

SELECT table_name,
data_length,
index_length,
data_free,
ROUND(data_free / (data_length + index_length) * 100, 2) AS fragmentation_pct
FROM information_schema.tables
WHERE table_schema = 'your_database'
AND table_type = 'BASE TABLE'
ORDER BY fragmentation_pct DESC;

Fix Fragmentation

Method 1 — OPTIMIZE TABLE (Rebuild, like SQL Server REBUILD)

Drops and recreates the table's clustered index and secondary indexes. Eliminates all fragmentation. More
expensive — locks may occur on older MySQL versions; MySQL 5.6+ uses online DDL.

-- Full rebuild — removes fragmentation, reclaims space, updates statistics

OPTIMIZE TABLE customers;

Method 2 — ALTER TABLE ... ENGINE=InnoDB (Alternative Rebuild)

Page 17
SQL Indexes — Complete Notes (MySQL Edition)

Equivalent to OPTIMIZE TABLE for InnoDB:

ALTER TABLE customers ENGINE=InnoDB;

■ Note

Fragmentation Thresholds (Guideline) — - 0–10% fragmentation → No action needed - 10–30%

fragmentation → Consider OPTIMIZE (equivalent to REORGANIZE in SQL Server) - >30% fragmentation
→ Run OPTIMIZE TABLE / ALTER TABLE ENGINE=InnoDB (equivalent to REBUILD)

Summary of Maintenance Tasks

direction: LR
Monitor Index Usage -> Drop Unused Indexes
Drop Unused Indexes -> Find Missing Indexes
Find Missing Indexes -> Add Missing Indexes
Add Missing Indexes -> Find Duplicate Indexes
Find Duplicate Indexes -> Remove Duplicates
Remove Duplicates -> Update Statistics (ANALYZE TABLE)
Update Statistics (ANALYZE TABLE) -> Check Fragmentation
Check Fragmentation -> OPTIMIZE TABLE if needed
OPTIMIZE TABLE if needed -> Monitor Index Usage

Part 10: Indexing Strategy

The Golden Rule — Avoid Over-Indexing

■ Important

Golden Rule — Less is more. Avoid over-indexing. Every index you add slows down INSERT, UPDATE,
and DELETE operations. Having too many indexes also makes it harder for the optimizer to choose the best
execution plan — it can actually cause slower queries because the optimizer gets confused.

Over-indexing leads to:

• Slower write operations (every write updates every index)

• Confused optimizer — harder to pick the best plan
• Wasted storage
• Bad execution plans chosen by the optimizer

Write this in your team's development guidelines: Avoid over-indexing. Every index must serve a clear
purpose.

Page 18
SQL Indexes — Complete Notes (MySQL Edition)

Four-Phase Indexing Strategy

Phase 1 — Initial Indexing Strategy

Before writing a single CREATE INDEX statement, define the goal of your indexing strategy by
understanding what type of system you are building.

OLAP System (Online Analytical Processing)

Examples: Data warehouses, BI dashboards, reporting systems

• Data is typically written once (nightly ETL), then read heavily

• Reports generate large, complex aggregation queries
• Main pain point: read performance
• Strategy: Use column store indexes on large fact tables; clustered indexes on primary keys elsewhere

OLTP System (Online Transaction Processing)

Examples: E-commerce, banking, ERP, mobile apps

• Constant stream of small reads and writes (every user action = a transaction)
• Main pain point: write performance (must keep up with high transaction volume)
• Strategy: Use clustered indexes on primary keys; be very conservative with secondary indexes; each
added index costs write performance

OLAP Strategy:
- Fact tables → Column store index
- Dimension tables → Clustered index on PK
- Secondary indexes → Only where heavily used for joins/filters

OLTP Strategy:
- All tables → Clustered index on PK (AUTO_INCREMENT)
- Secondary indexes → Foreign keys, frequently filtered columns only
- Be very careful — each index slows writes

Phase 2 — Usage-Pattern Indexing

Now do a deep dive into your actual queries to identify:

• Frequently used tables — which tables appear in the most queries?

• Frequently used columns — which columns appear in WHERE, JOIN ON, GROUP BY, ORDER BY?
• Column usage type — is it filtering, joining, aggregating, or sorting?

Practical approach: Use AI tools or manual analysis to scan all your SQL scripts. For each table, catalog:

• How many queries use it

• Which columns are used for filtering
• Which columns are used for joins
• Which columns are used for aggregations

Page 19
SQL Indexes — Complete Notes (MySQL Edition)

Once you have this analysis, map it to index types using the "Choosing the Right Index" guide from Part 8.

Then test your new indexes — run EXPLAIN on your queries before and after to confirm the index is
actually used and improves performance.

Phase 3 — Scenario-Based Indexing (Fix Slow Queries)

After the general strategy is in place, focus on specific slow queries:

• Identify slow queries — collect reports from users or analyze the MySQL slow query log
• Analyze with EXPLAIN — run EXPLAIN on the slow query; look for:
• type = ALL (full table scan — no index used)
• Extra = Using filesort (expensive sort, possibly needs an index)
• rows column — high row estimates indicate inefficient access
• Choose the right index — based on what EXPLAIN tells you, decide what index to add
• Create and test — add the index, run EXPLAIN again, compare before and after

-- Step 1: Identify slow queries in MySQL slow query log

-- (Enable in [Link]: slow_query_log=1, long_query_time=1)

-- Step 2: Run EXPLAIN on a slow query

EXPLAIN
SELECT c.customer_id, c.first_name, SUM([Link])
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE [Link] = 'USA'
AND o.order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY c.customer_id, c.first_name;

-- Step 3: Based on EXPLAIN output, create targeted indexes

CREATE INDEX idx_customers_country ON customers (country);
CREATE INDEX idx_orders_date_customer ON orders (order_date, customer_id);

-- Step 4: Run EXPLAIN again and compare — confirm indexes are used

■ Note

Indexing Is Not the Only Tool — If adding an index doesn't solve a slow query, other techniques may help:
query rewriting, JOIN order optimization, adding covering indexes (include all needed columns in the index),
partitioning, or query caching.

Phase 4 — Ongoing Monitoring and Maintenance

This phase never ends. Continuously:

Page 20
SQL Indexes — Complete Notes (MySQL Edition)

Task How Often MySQL Tool

Monitor index usage Weekly / monthly performance_schema.table_io

_waits_summary_by_index_usa
ge

Check for missing indexes After slow query reports EXPLAIN, slow query log

Check for duplicate indexes After each sprint / quarterly information_schema.statisti

cs

Update statistics Weekly (scheduled job) + after bulk ANALYZE TABLE

loads

Check and fix fragmentation Monthly information_schema.tables

(data_free), OPTIMIZE TABLE

■ Tip

Build a Monitoring Dashboard — For production systems, extract these metadata queries into an automated
dashboard (Power BI, Grafana, or any BI tool) connected to information_schema and
performance_schema. This lets you monitor the health of all indexes at a glance without running queries
manually.

The Full Strategy Cycle

direction: TB
Phase 1: Define Goal (OLAP vs OLTP) -> Phase 2: Identify Usage Patterns
Phase 2: Identify Usage Patterns -> Choose Right Index Type
Choose Right Index Type -> Create and Test Indexes
Create and Test Indexes -> Phase 3: Fix Slow Queries (EXPLAIN)
Phase 3: Fix Slow Queries (EXPLAIN) -> Phase 4: Monitor and Maintain
Phase 4: Monitor and Maintain -> Phase 1: Define Goal (OLAP vs OLTP)

The cycle repeats. As data grows, new queries are added, and requirements change — revisit the strategy
regularly.

Quick Reference — MySQL Index Syntax Cheat Sheet

-- ■■ CLUSTERED INDEX (Primary Key) ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

-- Automatically created when you define PRIMARY KEY in InnoDB
CREATE TABLE example (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
);

Page 21
SQL Indexes — Complete Notes (MySQL Edition)

-- ■■ NON-CLUSTERED (SECONDARY) INDEX ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

CREATE INDEX idx_name ON table_name (column1);

-- Composite index
CREATE INDEX idx_name ON table_name (column1, column2);

-- ■■ UNIQUE INDEX ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

CREATE UNIQUE INDEX idx_name ON table_name (column1);

-- ■■ FILTERED (PARTIAL) INDEX via Generated Column ■■■■■■■■■■■■■■■■■

ALTER TABLE table_name
ADD COLUMN gen_col TYPE
GENERATED ALWAYS AS (IF(condition, value, NULL)) STORED;
CREATE INDEX idx_name ON table_name (gen_col);

-- ■■ DROP INDEX ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

DROP INDEX idx_name ON table_name;

-- ■■ VIEW INDEXES ON A TABLE ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

SHOW INDEX FROM table_name;

-- ■■ CHECK EXECUTION PLAN ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

EXPLAIN SELECT ...;

-- ■■ UPDATE STATISTICS ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

ANALYZE TABLE table_name;

-- ■■ FIX FRAGMENTATION ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

OPTIMIZE TABLE table_name;
-- or
ALTER TABLE table_name ENGINE=InnoDB;

-- ■■ MONITOR INDEX USAGE ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

SELECT object_name, index_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_db'
ORDER BY count_star ASC;

-- ■■ MONITOR FRAGMENTATION ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

SELECT table_name, data_free,
ROUND(data_free / (data_length + index_length) * 100, 2) AS frag_pct
FROM information_schema.tables
WHERE table_schema = 'your_db'
ORDER BY frag_pct DESC;

-- ■■ FIND SLOW QUERIES ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

Page 22
SQL Indexes — Complete Notes (MySQL Edition)

SELECT digest_text, count_star, avg_timer_wait/1000000000 AS avg_ms

FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 20;

Notes based on SQL Indexes course — concepts converted to MySQL/InnoDB equivalents.

Page 23