Chapter 7. Indexing Strategies

Like beauty, the most attractive indexing strategy is very much in the eye of the beholder. After indexes are in place for primary, join, and filter keys (a universal standard of indexing beauty, perhaps?), what works for application A might be the wrong approach for appli-cation B.

Application A might be a transactional system that supports tens of thousands of quick interactions with the database, and its data modifications must be made in milliseconds. Application B might be a decision support system in which users create an ample assortment of server-hogging queries. These two applications require very different indexing tactics.

In addition, MySQL's optimizer always tries to use the information at hand to develop the most efficient query plans. However, requirements change over time; users and applications can introduce unpredicted requests at any point. These requests might include new transactions, reports, integration, and so forth.

As a database designer or administrator, it's your job to continually examine your database and its indexes, being prepared to make alterations when applicable. The good news is that with the exception of server parameters, indexes are the easiest database structures to modify without causing havoc in your applications. If you forget an index prior to going into production, it's a relatively simple manner to add it without changing a line of code (although there might be a time cost to generating indexes on large tables).

This chapter investigates MySQL's index capabilities. It begins by reviewing how indexes work, as well as their structure. Next, it reviews indexing features specific to each of the major MySQL data storage engines. This chapter does not spend much time on actually tuning the storage engines' index capabilities; this comes later in the chapters dedicated to each of these engines.

This chapter then examines a broad range of situations in which indexes might help speed up your application. In addition to examining how indexes can be of assistance, this chapter also discusses circumstances in which indexes might actually damage performance.

Before commencing the analysis, let's review the example strategy. As described previously, this book always strives to make the examples as simple as possible while still illustrating a particular point. This means that for clarity's sake, some of the examples might violate suggestions made in different parts of the chapter and book. However, every example is relevant for the topic under review.