Databases Reference
In-Depth Information
CHAPTER 5
Indexing for High Performance
Indexes (also called “keys” in MySQL) are data structures that storage engines use to
find rows quickly. They also have several other beneficial properties that we'll explore
in this chapter.
Indexes are critical for good performance, and become more important as your data
grows larger. Small, lightly loaded databases often perform well even without proper
indexes, but as the dataset grows, performance can drop very quickly.
1
Unfortunately,
indexes are often forgotten or misunderstood, so poor indexing is a leading cause of
real-world performance problems. That's why we put this material early in the book—
even earlier than our discussion of query optimization.
Index optimization is perhaps the most powerful way to improve query performance.
Indexes can improve performance by many orders of magnitude, and optimal indexes
can sometimes boost performance about two orders of magnitude more than indexes
that are merely “good.” Creating truly optimal indexes will often require you to rewrite
queries, so this chapter and the next one are closely related.
Indexing Basics
The easiest way to understand how an index works in MySQL is to think about the
index in a topic. To find out where a particular topic is discussed in a topic, you look
in the index, and it tells you the page number(s) where that term appears.
In MySQL, a storage engine uses indexes in a similar way. It searches the index's data
structure for a value. When it finds a match, it can find the row that contains the match.
Suppose you run the following query:
mysql>
SELECT first_name FROM sakila.actor WHERE actor_id = 5;
1. This chapter assumes you're using conventional hard drives, unless otherwise stated. Solid-state drives
have different performance characteristics, which we cover throughout this topic. The indexing principles
remain true, but the penalties we're trying to avoid aren't as large with solid-state drives as they are with
conventional drives.
