High Performance MySQL, 4th Edition

Chapter 7. 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 data set grows, performance can drop very quickly.¹ 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.

This chapter relies on using example databases, like the Sakila Sample Database, available from MySQL’s website. Sakila is an example database that models a rental store, with a collection of actors, films, customers, and more.

Indexing Basics

The easiest way to understand how an index works in MySQL is to think about the index of a book. To find out where a particular topic is discussed in a book, ...