Understanding MySQL’s Three Index Processing Stages: Key, Filter, and Table

1. Index Key

Definition: The index key is the column(s) stored in the index and is used to locate rows that satisfy the most basic conditions.

Use case: When the query predicate matches the indexed column exactly, e.g.:

SELECT * FROM employees WHERE employee_id = 123;

Characteristics:

The basic function of an index: direct location of matching rows.

Retrieves rows that match exactly or fall within a range.

Query execution flow:

Quickly locate the candidate record set via the index key.

2. Index Filter

Definition: Index filter performs additional filtering on the data stored in the index without fetching the full rows, and is the core of Index Condition Pushdown (ICP).

Use case: When the WHERE clause involves multiple columns but not all can be satisfied by the index key alone, e.g.:

SELECT * FROM employees WHERE employee_id > 100 AND salary < 50000;

Assuming a composite index (employee_id, salary):

The engine uses employee_id > 100 to locate a range of rows.

Then, still in the storage engine, it applies salary < 50000 to filter those rows, avoiding sending all matches back to the server.

Characteristics:

Filters data directly in the index storage layer.

Reduces the amount of data that needs to be fetched and processed by the server.

Query execution flow:

Locate candidate rows using the index key.

Further filter those candidates in the storage engine.

3. Table Filter

Definition: Table filter occurs after the rows have been fetched (a “row lookup” or “table access”) and filters on columns that are not covered by the index.

Use case: When the WHERE clause references non‑indexed columns, e.g.:

SELECT * FROM employees WHERE employee_id > 100 AND department = 'Engineering';

Assuming only an index on employee_id:

The engine uses the index to find rows where employee_id > 100.

After the rows are retrieved, the server evaluates department = 'Engineering' to filter the result set.

Characteristics:

Filtering happens at the server layer after a row lookup.

If many non‑indexed columns are involved, table filtering can become a performance bottleneck.

Query execution flow:

Locate candidate rows via the index key.

Fetch the full rows (row lookup).

Apply remaining predicates on the server.

Three Physical Filtering Processes

Index Key : Initial stage, quickly locate candidate rows using the index.

Index Filter : Further filter candidates in the storage engine, reducing rows that need to be fetched.

Table Filter : Final server‑side filtering for non‑indexed columns or complex conditions.

Key Points of Index Condition Pushdown

Before MySQL 5.6, all complex filtering was performed in the server layer.

Since MySQL 5.6, ICP pushes the Index Filter step down to the storage engine, decreasing the number of rows that need to be returned to the server and improving query performance.

Example

Given a table with a composite index (employee_id, salary) and the query:

SELECT * FROM employees WHERE employee_id > 100 AND salary < 50000 AND department = 'Engineering';

Index Key : Uses employee_id > 100 for a range scan.

Index Filter : Applies salary < 50000 in the storage engine, reducing rows sent to the server.

Table Filter : After rows are fetched, the server evaluates department = 'Engineering'.

Conclusion

Index Condition Pushdown leverages the Index Filter step in the storage layer to cut down on row lookups and server‑side processing, and a well‑designed covering index can further minimize the need for table filtering, leading to faster queries.