MySQLoptimizer

MySQL 5.0 index merge

When the optimizer merges multiple indexes — and when a composite index still wins.

MySQL 5.0 made index merge visible: use more than one index on a table and merge (intersection for AND, union for OR).

EXPLAIN SELECT * FROM people
WHERE last_name = 'Lentz' AND city = 'Metro';
-- type: index_merge
-- key: idx_last_name,idx_city

Intersection can beat a scan when each predicate is selective. It still reads multiple structures and merges row IDs.

Prefer composite when the AND chain is stable

ALTER TABLE people
  ADD INDEX idx_last_city (last_name, city);

One structured lookup usually beats merge gymnastics.

Union merge helps awkward OR shapes. A single status index plus IN (...) is often clearer.

See index_merge in EXPLAIN, then ask whether a composite matches the real AND chain. Keep merges for cases you cannot normalise.