High Performance MySQL

Chapter 4. Indexes

Indexes allow MySQL to quickly find and retrieve a set of records from the millions or even billions that a table may contain. If you’ve been using MySQL for any length of time, you’ve probably created indexes in the hopes of getting lighting-quick answers to your queries. And you’ve probably been surprised to find that MySQL didn’t always use the index you thought it would.

For many users, indexes are something of a black art. Sometimes they work wonders, and other times they seem just to slow down inserts and get in the way. And then there are the times when they work fine for a while, then begin to slowly degrade.

In this chapter, we’ll begin by looking at some of the concepts behind indexing and the various types of indexes MySQL provides. From there, we’ll cover some of the specifics in MySQL’s implementation of indexes. The chapter concludes with recommendations for selecting columns to index and the longer term care and feeding of your indexes.

Indexing Basics

To understand how MySQL uses indexes, it’s best first to understand the basic workings and features of indexes. Once you have a basic understanding of their characteristics, you can start to make more intelligent choices about the right way to use them.

Index Concepts

To understand what indexes allow MySQL to do, it’s best to think about how MySQL works to answer a query. Imagine that phone_book is a table containing an aggregate phone book for the state of California, with roughly 35 million entries. ...