MySQL Cookbook, 4th Edition

Chapter 21. Query Performance

21.0 Introduction

Indexes are utilized to find rows quickly if they are created and used as intended. Here are the main reasons to use indexes:

• Utilize a WHERE clause in a SELECT statement to efficiently find rows.

• Find the best query execution plan by the index’s uniqueness of values stored in a given column, known as cardinality, and the least number of rows returned.

• Enable the join operations between different tables.

Indexes are vital to efficiently scanning and searching for values in tables. Without them, MySQL would need to read all of the rows in a given table when performing a query. Due to different table sizes, MySQL has to bring all the data read from the table to memory, and it can sort, filter, and return values only of the selected data. This operation may require additional resources to copy data to a new temporary table to perform sort operations. Indexes are crucial to query performance; hence, nonindexed tables are a considerable overhead to a database unless they are small reference tables.

For fast query performance, a primary key for each table representing one or more columns is required. While using the InnoDB storage engine, the table’s data is physically ordered to do fast lookups and sorts using primary key columns. The ideal table design uses a covering index where the query results are computed using index columns. Most of the indexes used by MySQL are stored in B-trees, which allow fast data access due to reduced ...