Multi-column indexes are often poorly understood. Common mistakes are to set many columns to separate indexes, or to index columns in the wrong order. In the next article, we will discuss the order of the index column. First, we will look at the case of multiple independent indexes, as the following table structure:

CREATE TABLE test (
  c1 INT,
  c2 INT,
  c3 INT,
  KEY(c1),
  KEY(c2),
  KEY(c3),
);
Copy the code

Using this indexing strategy is usually the result of some authoritative advice, such as adding indexes to the condition columns used in the WHERE condition. In fact, it’s so wrong that it’s one star at most. Indexing in this way is orders of magnitude slower than a truly optimized index. Sometimes when you can’t design an index of more than three stars, it’s better to focus on optimizing row order or creating overridden indexes than to ignore WHERE conditions.

A covering index means that the execution of a query can be obtained only from the index, not from the data table. You can also call it index coverage. When a query statement meets the conditions of overwriting an index, MySQL can return the data required by the query only through the index. In this way, the operation of returning to the table after the index is queried is avoided, which reduces I/O and improves efficiency. For example, the table covering_index_SAMPLE has a common index IDx_KEY1_KEY2 (key1,key2). Select key2 from covering_index_SAMPLE where key1 = ‘keytest’; Instead of looking for rows from the table, you can override the index query.

Creating a separate index for many columns in many cases does not help MySQL improve performance. MySQL 5.0 and later can optimize this design a bit with the index merge strategy, which allows queries in tables with multiple indexes to limit the use of indexes to locate the desired rows.

Index merge is a conditional scan of multiple indexes and a merge of their respective results (INTERSECT/Union)

Earlier versions of MySQL could only use one index, so MySQL usually did a full table scan when there was no index assist. For example, the film_actor table has a film_id and actor_id index, but using both indexes in the WHERE condition is not a good choice:

SELECT film_id, actor_id FROM film_actor WHERE actor_id = 1 OR film_id = 1;
Copy the code

In earlier versions of MySQL, this query would cause a full table scan unless you joined the two queries as in the following statement.

SELECT film_id, actor_id FROM film_actor WHERE actor_id = 1 UNION ALL 
SELECT film_id, actor_id FROM film_actor WHERE film_id = 1 AND actor_id <> 1;
Copy the code

In MySQL versions after 5.0, queries use both indexes and merge the final results. Three variants of the algorithm are required to implement this process:

  1. Get union data using an OR condition
  2. Get intersection data using the AND condition
  3. Reunion the intersection of the data from the previous two steps.

The above is a bit confusing, but the distribution should use a single condition (to use an index) to find out all the data, and then combine the data. Let’s take a look using EXPLAIN.

EXPLAIN SELECT `film_id`,`actor_id` FROM `film_actor` WHERE `actor_id`=1 OR `film_id`=1
Copy the code

You can see that the query mode is full table scan, but using Extra to optimize. MySQL uses this technique when handling responsible queries, so you might see nested operations in Extra. This strategy of index merging sometimes works well, but more often it should be used as an indication of poor index use:

  1. When a server uses an intersection index (usually using an AND condition), it usually means that you need an index that contains all related columns, not a combination of individual index columns.
  2. When a server uses union indexes (usually using OR conditions), sometimes caching, sorting, and merging operations can consume a lot of CPU and memory resources, especially when indexes are not all filtered, resulting in scans returning a large number of rows for merging operations.
  3. Remember that the optimizer does not bear these costs — it simply optimizes the number of random page reads. This will “degrade” the query and cause the full table scan to be actually slower. High CPU and memory usage can affect concurrent queries, but these effects do not occur when you run queries alone. Therefore, it is sometimes better to rewrite queries that use unions as in MySQL version 4.1.

When you see index merges when using EXPLAIN analysis, you should examine the query statement and table structure to see if this is the best way to do it. You can check this by disabling index merging using optimizer_switch.

Select film_actor as the joint index (film_id and actor_id).

ALTER TABLE film_actor ADD INDEX `sindex` (`film_id`,`actor_id`);
Copy the code