Name
PER-02: Carefully create the best set of indexes for your application
Synopsis
The primary purpose of indexes is to allow MySQL to rapidly retrieve the information you need. Just as the index in this book allows you to find some information without having to read the entire book, an index allows MySQL to get rows from the table without reading the entire table.
Determining the optimal set of indexes for your application
is, therefore, probably the single most important step you can
take to optimize MySQL stored program performance. In general, you
should create indexes that support WHERE
clause conditions and joins. You
should also create multicolumn (concatenated) indexes, so that a
single index can support all of the columns in the WHERE
clause or all of the columns
required to join two tables.
You should create indexes to support joins, since without an appropriate index, joins will degrade rapidly as the row counts in the involved tables increase.
Get MySQL Stored Procedure Programming now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.