In this chapter we examined some of the basic principles for tuning simple SQL statements. Tuning SQL inside of MySQL stored programs is probably the single most important thing we can do to avoid poorly performing stored programs.
For SQL statements that retrieve a small proportion of the rows from a table (say, 5 to 15%), you will probably want to create indexes to obtain good performance. Here are some best practice guidelines for creating indexes:
Create concatenated indexes that include all of the columns
referenced in the
Consider adding additional columns that appear in the
SELECT list to allow for an
“index only” access path.
Create concatenated indexes to support the widest possible
range of queries—concatenated indexes can be used for queries that
reference only a subset of the columns in the index, provided that
the “leading” columns are in the
WHERE clause. This means that you should
put the most commonly used columns first in the index.
If the number of concatenated indexes needed to support all possible queries is too large (say five or more), create single-column indexes on selective columns that MySQL can merge.
MySQL can join large tables effectively only if an index exists on the join columns for at least one of the tables being joined. To optimize basic joins:
Create a concatenated index on all of the columns used to join the two tables.
Make sure that any
WHERE clause conditions are executed before the tables are joined. That is, the “driving ...