Why MySQL OR Queries Often Skip Indexes—and How to Fix Them

MySQL OR Condition Queries

The OR logical operator connects multiple predicates in a WHERE clause. A row is returned if any of the predicates is true. This operator is frequently used for multi‑field searches, range conditions, and dynamic filtering.

Typical OR Query Examples

Multi‑field query:

SELECT * FROM users WHERE name = 'Alice' OR city = 'Chicago';

Range query: SELECT * FROM users WHERE age < 30 OR age > 35; Dynamic filter:

SELECT * FROM orders WHERE status = 'pending' OR status = 'processing';

Index Skipping with OR

If any operand in an OR expression lacks an index, MySQL may fall back to a full table scan even when other operands are indexed. The optimizer cannot efficiently combine indexed and non‑indexed columns in a single OR condition.

Example Table

CREATE TABLE example_table (
  id INT NOT NULL,
  name VARCHAR(100),          -- no index
  age INT,                     -- indexed
  PRIMARY KEY (id),
  INDEX index_age (age)
);

INSERT INTO example_table (id, name, age) VALUES
  (1, 'Alice',   30),
  (2, 'Bob',     25),
  (3, 'Charlie', 35),
  (4, 'Dave',    40);

Query Using OR and EXPLAIN

EXPLAIN SELECT * FROM example_table WHERE id = 1 OR name = 'Alice' \G

The execution plan shows type: ALL and no index used because the name column is not indexed, causing a full table scan.

Solution 1 – Replace OR with UNION

Split the original query into two separate queries, each able to use the appropriate index, and combine the results with UNION (or UNION ALL if duplicates are acceptable).

SELECT * FROM example_table WHERE id = 1
UNION ALL
SELECT * FROM example_table WHERE name = 'Alice';

The first part uses the PRIMARY index on id. The second part still scans because name is unindexed, but for large data sets the overall cost can be lower than a single full scan.

Solution 2 – Add Missing Index

ALTER TABLE example_table ADD INDEX index_name (name);

After adding the index, re‑run the EXPLAIN:

EXPLAIN SELECT * FROM example_table WHERE id = 1 OR name = 'Alice' \G

The plan now shows type: index_merge and uses both PRIMARY and index_name, allowing MySQL to combine indexes and avoid a full scan.

Practical Guidelines

Using OR when any operand lacks an index often forces a full table scan.

The MySQL optimizer handles OR less efficiently than AND.

Avoid OR where possible; rewrite the logic with UNION / UNION ALL or create appropriate indexes.

When multiple indexes are available, the index_merge strategy can combine them and significantly improve performance on large tables.