MySQL Stored Procedure Programming

Name

PER-04: Optimize necessary table scans

Synopsis

Using an index to retrieve rows from a table is worthwhile only when you are retrieving a relatively small subset of rows from the table. Over a certain proportion of the table (say 5-20%), it is more efficient to read every row from the table. However, it is still possible to optimize these “necessary” table scans. For instance:

• You can move long, infrequently accessed columns to a secondary table.

• You can create an index on all of the columns required for the query. MySQL can then scan the entire index to resolve the query. Since the index will normally be smaller than the table, it ought to be quicker to scan the index.

These techniques are discussed in detail in Chapter 21.