What is type index_merge in MySQL EXPLAIN?

Performance Classification: depends

The index_merge strategy is something you have probably not seen very often yet but it is really genius: Multiple indexes can be used to find rows for loading which is a significant performance boost compared to just a single index. However, in most situations creating a better index with multiple columns that match your conditions is recommended!

Examples

✅ When it is used

CREATE TABLE employees (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(100) NOT NULL,
    country char(2) NOT NULL,
    department varchar(100) NOT NULL,
    INDEX(country),
    INDEX(department)
);

SELECT * FROM employees WHERE country = 'ZZ' AND department = 'R&D';

The most common use of index merging is conditions on multiple conditions that all have to match - identified by Using intersect(...) in the Extra column. First, the indexes on country and department are used one after another to identify matching employee rows. Then, those results are combined (merged) by only keeping references to rows that matched both times. Only this result is used to load rows by the index. However, a multi-column index on (country,department) would be much faster as just one index needs to be used and the time-consuming merging procedure is not needed.

CREATE TABLE transactions (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id bigint NOT NULL,
    amount numeric(8,2) NOT NULL,
    reference varchar(50) NOT NULL,
    routing varchar(50) NOT NULL,
    INDEX(reference),
    INDEX(routing)
);

SELECT * FROM transactions WHERE reference = 'B-4Z0' OR routing = 'B-4Z0';

The index merge can also be applied for multiple conditions combined by OR and is identified by the Extra column listing the Using union(...) information. Creating an index for these is impossible and you always have to make schema changes so you don't have to use OR - so it is a welcome optimization by MySQL.

CREATE TABLE inventory (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    product_id bigint NOT NULL,
    warehouse_id bigint NOT NULL,
    stock int UNSIGNED NOT NULL,
    delivery_in_days int UNSIGNED NOT NULL,
    INDEX(stock),
    INDEX(delivery_in_days)
);

SELECT * FROM inventory WHERE stock < 10 OR delivery_in_days < 3;

MySQL can also use an index merge for ranges combined with OR which will be highlighted by Using sort_union(...) in the Extra column.

❌ When it is not used

CREATE TABLE articles (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    category varchar(1000) NOT NULL,
    title varchar(1000) NOT NULL,
    content text NOT NULL,
    INDEX(category_id),
    FULLTEXT(content)
);

SELECT *
FROM articles
WHERE category = 'tech' AND MATCH(content) AGAINST('mysql database');

Full-text indexes are a special type in MySQL with many limitations. Therefore, index merging to filter results from a full-text search is not supported.

How to optimize a query using `index_merge`?

Getting MySQL to not use index_merge is easy when the indexed columns are combined with AND:

Add a new multi-column index that includes all important columns of the used indexes

However, when using OR it is more complicated as you can't build a single index that will be used to fulfill the query - OR is hard to optimize. So in this case, the index_merge is a good optimization you are happy with. But you can also try this trick to make it faster:

Create a new column that serializes your OR condition to a zero or one value representing whether the condition matches. Then, index this column and use it within your query instead of the OR condition.

Conclusion

The index_merge strategy is a very great approach of MySQL to combine multiple indexes and execute the query somewhat fast instead of doing a full-table scan. However, with this strategy is not as fast as the query could be. You can create a multi-column index that combines these indexes - if those are connected with AND. But schema changes are required if OR is used - or it is impossible.