Master MySQL Index Execution: From Index Key to Table Filter

Core Idea: Maximize work in the index layer to avoid costly table lookups. The three logical stages are Index Key → Index Filter (ICP) → Table Filter .

Example Schema and Query

Create a table with a composite index on age, salary and dept:

CREATE TABLE `user` (
  `id` INT PRIMARY KEY,
  `name` VARCHAR(100),
  `age` INT,
  `salary` INT,
  `dept` VARCHAR(10),
  INDEX `idx_age_salary_dept` (`age`, `salary`, `dept`)
);

Query that uses the index:

SELECT * FROM `user`
WHERE `age` = 30
  AND `salary` > 50000
  AND `dept` = 'IT'
  AND `name` LIKE 'A%';

Three‑Stage Execution Overview

┌───────────────────────┐
            │      Query Request     │
            └────────────┬──────────┘
                         │
                         ▼
            [1] Index Key   (determine scan range)
                         │
                         ▼
            [2] Index Filter (ICP – filter inside index)
                         │
                         ▼
            [3] Table Filter (row lookup + final WHERE)
                         │
                         ▼
                Return Result Set

Stage 1 – Index Key (Index Positioning)

Purpose: Define the start and end boundaries of the index scan.

Rules

Start with the leftmost column of the index.

Use consecutive equality (=) or range (>, <, BETWEEN, IN) conditions.

Once a range condition appears, later columns cannot contribute to range determination.

Example Analysis

age = 30

– equality match, used for positioning. salary > 50000 – range match; after this, dept cannot extend the range.

Result: MySQL scans only the part of the index where age=30 AND salary>50000.

Smaller scan ranges yield better performance.

Stage 2 – Index Filter (ICP – Index Condition Pushdown)

Purpose: Within the scanned range, filter rows using remaining indexed columns, reducing data sent to the server.

Background

MySQL 5.6 introduced Index Condition Pushdown, allowing the storage engine to evaluate some WHERE predicates during index scanning.

Rules

Applicable to indexed columns not used for range determination.

Evaluation occurs at the storage‑engine layer.

Reduces the number of rows returned to the server.

Example Analysis

dept = 'IT'

is indexed but not used in the Index Key stage.

ICP filters this condition while scanning the index.

Resulting index entries: (age=30, salary>50000, dept='IT').

Stage 3 – Table Filter

Purpose: Apply remaining predicates that cannot be evaluated in the index, typically requiring a row lookup (back‑table).

Rules

Condition not present in the index.

Complex expressions that the index cannot evaluate.

Filtering is performed in the server after fetching the full row.

Example Analysis

name LIKE 'A%'

is not part of the index.

MySQL fetches the row using the primary key id, then checks the name pattern.

This is the most expensive stage and should be minimized.

EXPLAIN Output and Interpretation

Run:

EXPLAIN SELECT * FROM `user`
WHERE age = 30 AND salary > 50000 AND dept = 'IT' AND name LIKE 'A%'
\G

Typical relevant fields:

id: 1
select_type: SIMPLE
table: user
type: range
possible_keys: idx_age_salary_dept
key: idx_age_salary_dept
key_len: 14
rows: 120
Extra: Using index condition; Using where

Using index condition

– ICP (Index Filter) is active. Using where – Remaining predicate ( name LIKE 'A%') runs in Table Filter. type: range – Index range scan is used. rows: 120 – Estimated number of index entries scanned.

Performance Experiment: Covering vs Non‑Covering Index

Two queries:

-- Non‑covering (requires back‑table)
SELECT * FROM user
WHERE age=30 AND salary>50000 AND dept='IT';

-- Covering (all columns in index)
SELECT age, salary, dept FROM user
WHERE age=30 AND salary>50000 AND dept='IT';

Observed results (example):

Non‑covering: table lookup = ✅, rows scanned ≈ 120, execution time ≈ 15.8 ms.

Covering: no table lookup, rows scanned ≈ 12, execution time ≈ 1.3 ms.

Covering index query is more than ten times faster.

Index‑Optimization Checklist

Equality first: Place equality conditions before range conditions; a range stops further index usage.

Column order: Put the most selective columns early in the index.

Covering index: Include all columns needed by the query in the index when possible.

Enable ICP: MySQL ≥ 5.6 automatically uses Index Filter.

Avoid functions / implicit conversions: They can prevent index usage.

Use EXPLAIN + SHOW PROFILE: Identify where filtering occurs and locate bottlenecks.

Source‑Level Execution Difference

Before MySQL 5.6 the storage engine only scanned the index and returned all matching rows to the server, which performed all filtering.

Since MySQL 5.6 the storage engine can evaluate part of the WHERE clause (ICP) during the index scan, sending fewer rows to the server and reducing back‑table operations.

Result: lower CPU and I/O load, especially for large index scans.

Conclusion

Understanding the three stages— Index Key , Index Filter (ICP) , and Table Filter —lets you design indexes that keep most predicates in the index layer, dramatically improving query performance.