Comparison of index merges and composite indexes

In the course of the development of the subsystem of the teaching system, the algorithm of arranging the examination room is very slow, it takes 32 seconds to complete the allocation of the examination room. Find out why, because you need to look up data in the student make-up chart-the table has 2 million of data, so the algorithm spends most of its time looking for data. To solve the above problem, we decided to analyze the data table and create the appropriate index.

During the creation of the index, we found that the query statement already created an index for the query column, but it was created separately for each column--select * FROM table_name WHERE col1= ' ABC ' and col2=123;

To focus on the principle, I simplified the example. In the above example, the query criteria for the queries are 2 columns, COL1 and COL2 respectively. We created an index for COL1 (INDEX1) and COL2 (INDEX2) respectively. After creating the index, we tested to see if the speed was not significantly accelerated or it took more than 20 seconds. The optimization effect is not obvious.

We then tried to create a composite index on COL1 and COL2, which was very effective, from 32 seconds to the current 8 seconds. In this case, I delve into the creation of a separate index on 2 columns, called a merge index if used by a query, or a combined index if you are creating a composite index on 2 columns. The following principles explain the difference between a combined index and a combined index, and also explain why the combined index is much faster than the merged index.

First, explain the search steps for merging the indexes, as shown in the following illustration:

The steps above can be described in the following illustration:

As shown in the following illustration, a single 2 index lookup--an index merge--needs to be repeatedly jumped between 2 index tables, which is the first effect that slows down. The 2nd effect is to assume that the data that satisfies col1= ' ABC ' has 5 rows, and that the col2=123 data has 1000 rows. In the worst case scenario (those 5 lines at the end of the 1000 line in COL2) you need to scan the COL2 1000 lines to find the data you need, and you won't be able to find it quickly.

Let's talk a little bit about the combined index, because the composite index combines COL1 and COL2 data, it doesn't need to jump between 2 index tables, so it's faster, and the search steps for combining the indexes are as shown in the following illustration:

The above illustration is a comparison of index merges and composite indexes. Composite indexes do not need to compare the rowid of an index like an index merge.

The above is a description of how the index is merged into a composite index, and, in other cases, it is better to use a composite index in which case the combined index is most effective when an equivalent comparison is used for an indexed column in a query condition.