How Implicit Index Masking and Redundant Conditions Impact MySQL Query Performance

Implicit Index Masking

Indexes in MySQL are separate data structures—usually B+ trees—that allow fast data retrieval. When a query uses the indexed column directly, MySQL can employ the index, but wrapping the column in a function prevents the optimizer from using the index, a situation known as implicit index masking.

CREATE TABLE `todos` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `created_at` (`created_at`)
);

Querying recent rows with the index works as expected:

EXPLAIN SELECT * FROM todos WHERE created_at > NOW() - INTERVAL 24 HOUR \G
-- Output shows type=range, key=created_at, using index condition.

If the column is wrapped in YEAR(), the index is masked and MySQL falls back to a full table scan:

EXPLAIN SELECT * FROM todos WHERE YEAR(created_at) = 2023 \G
-- Output shows type=ALL, possible_keys=NULL, key=NULL.

Replacing the function with a range scan restores index usage:

EXPLAIN SELECT * FROM todos WHERE created_at BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59' \G
-- Output shows type=range, key=created_at, using index condition.

Redundant Conditions in MySQL

A redundant condition does not change the result set. For example, adding id < 10 to a query that already filters id < 5 is logically unnecessary:

SELECT * FROM todos WHERE id < 5 AND id < 10; -- No effect on results

Redundant conditions become useful when they enable the optimizer to use an index that would otherwise be ignored.

Consider a table with due_date (date) and due_time (time) columns, both indexed separately, and a query that adds them with ADDTIME():

SELECT * FROM todos WHERE ADDTIME(due_date, due_time) BETWEEN NOW() AND NOW() + INTERVAL 1 DAY;

Because the function operates on the indexed column, MySQL cannot use the due_date index. Adding a redundant condition on due_date that is broader but still true for the desired rows unlocks the index:

EXPLAIN SELECT * FROM todos
WHERE ADDTIME(due_date, due_time) BETWEEN NOW() AND NOW() + INTERVAL 1 DAY
  AND due_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 1 DAY \G
-- Output shows type=range, key=due_date, using index condition plus a where filter.

The redundant condition expands the result set slightly, but MySQL first uses the index to prune most rows, then applies the slower ADDTIME() filter to the remaining rows.

Domain‑Knowledge Redundant Conditions

When you know application‑level facts, you can add conditions that are not strictly logical redundancies but are guaranteed not to affect results. For instance, if created_at is always earlier than or equal to updated_at, you can query updated_at with an additional created_at filter to leverage the existing created_at index:

SELECT * FROM todos
WHERE updated_at < '2023-01-01 00:00:00'
  AND created_at < '2023-01-01 00:00:00';

This query still returns the same rows, but the optimizer can use the created_at index to eliminate most records before evaluating the updated_at predicate.

When to Use Redundant Conditions

Redundant conditions are most valuable when you cannot modify the schema to add a suitable index. By adjusting only the query (or the query‑generating code), you can achieve noticeable performance gains without any database changes, making this technique ideal for occasional or ad‑hoc queries.