MySQL Query optimization technology series of lectures on the use of the index

Indexing is the most important tool for improving query speed. Of course there are other technologies available, but generally the most significant performance difference is the correct use of the index. In the MySQL mailing list, people often ask about ways to make queries run faster. In most cases, we should suspect that there is no index on the datasheet and that the problem is usually resolved immediately after the index is added. Of course, it is not always easy to solve the problem, because the optimization technology is not always simple. However, if you are not using an index, in many cases you are wasting time trying to use other methods to improve performance. Use the index first to get maximum performance gains, and then see if other technologies are useful.

This section describes what the index is and how the index improves query performance. It also discusses the potential for indexes to degrade performance in some environments and provides guidelines for your judicious selection of indexes for datasheets. In the next installment we'll discuss the MySQL query optimizer, which tries to find the most efficient way to execute a query. Understanding some of the optimizer's knowledge, as a complement to how to build an index, is good for us because you can make better use of the indexes you have built. Some methods of writing a query actually make the index ineffective, and in general you should avoid this situation.

Advantages of indexing

Let's begin to understand how the index works, first with a datasheet without an index. A table without an index is just a unordered set of data rows. For example, the ad table shown in Figure 1 is a table with no indexes, so if you need to find a particular company, you must check each row of data in the table to see if it matches the target value. This results in a full datasheet scan, which can be slow, and if the table is large but contains only a few qualifying records, then the efficiency is very low.

Figure 1: An ad table without indexes

Figure 2 is the same data table, but adds an index to the Company_num data column for the ad table. This index contains entries for each data row in the ad table, but the entries for the index are sorted by the Company_num value. Now, instead of looking at the row-by-line to search for a matching data item, we use the index. Let's say we look for all the data rows for company 13. We started scanning the index and found three values for the company. Then we hit the index of the company 14, which is bigger than the value we're searching for. The index values are ordered, so when we read the index records containing 14, we know that there will be no more matching records to end the query operation. The effect of using an index is therefore that we have found where the matching rows of data are terminated and can ignore other rows of data. Another effect is to use the positioning algorithm to find the first matching entry without having to perform a linear scan from the index header (for example, a binary search is faster than a linear scan). By using this method, we can quickly locate the first matching value and save a lot of search time. The database uses a variety of techniques to quickly locate index values, but we don't care about these technologies in this article. The point is that they can be implemented, and indexing is a good thing.

Figure 2: Ad table after indexing