Conclusion

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 WHERE clause.
• 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 queriesconcatenated 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 table" should be the table that has the most selective condition in the WHERE clause. This will create the most efficient join.
• Provided that joins are supported by indexes and that WHERE clause conditions are processed in the first few tables to be joined, be aware that the best join order will be from smallest table to largest table.