Why OR Queries Break MySQL Indexes and How to Fix Them

In MySQL, the OR logical operator connects multiple conditions in the WHERE clause, returning rows that satisfy any of the conditions. While convenient, using OR can lead to index loss when some involved columns lack indexes, causing full table scans.

MySQL OR Condition Query Overview

The OR operator is often used for multi‑field queries, combined range queries, and dynamic filtering. Example queries:

SELECT * FROM users WHERE name = 'Alice' OR city = 'Chicago';
SELECT * FROM users WHERE age < 30 OR age > 35;
SELECT * FROM orders WHERE status = 'pending' OR status = 'processing';

Index Failure Case Analysis

When an OR query includes a condition on a column without an index, MySQL's optimizer may choose a full table scan (type: ALL) even if other conditions are indexed.

Example Analysis

Assume a table example_table with the following structure:

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

Insert test data:

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

Query using OR:

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

The optimizer uses the primary key for id = 1 but falls back to a full scan for name = 'Alice' because name lacks an index.

Solutions

Method 1: Replace OR with UNION

Split the original query and handle indexed and non‑indexed conditions separately using UNION to force index usage:

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

The first part uses the primary key index; the second part may still scan, but the overall plan can be more efficient for large datasets.

Method 2: Add an Index on the Non‑Indexed Column

If name is frequently queried, create an index to enable the optimizer to use index_merge:

ALTER TABLE example_table ADD INDEX index_name (name);
EXPLAIN SELECT * FROM example_table WHERE id = 1 OR name = 'Alice' \G

After adding the index, MySQL combines the primary key and the new index, improving performance and avoiding full scans.

Summary Rules

Using OR with unindexed columns often triggers full table scans.

The MySQL optimizer handles OR less efficiently than other operators.

Avoid OR when possible; prefer UNION ALL or UNION.

Create indexes on frequently filtered columns.

For complex queries, rewrite logic to split conditions and enable index usage.