Schema optimization and indexing

Objective

Optimizing a schema that is poorly designed or incorrectly indexed can improve performance in order of magnitude. If high performance is required, you must design schemas and indexes for the specific statements that will be executed. You should also evaluate performance for different kinds of statements, because modifying them into a statement or a part of a schema may lead to other knock-on effects. Optimization often involves a compromise. Adding an index, for example, increases the speed of the query, but slows the update. Similarly, non-canonical schemas can improve the execution speed of some statements, but may be reduced at other speeds. Adding count and summary tables is a good way to optimize the statements, but they increase the difficulty of maintenance.

Some times you may have gone beyond the role of the developer and you will have some questionable needs. Those who are not experts in database systems often write business requirements and do not consider performance. If you show that some small features will require twice the current server configuration, then they may decide to cancel the feature.

Schema and index optimization not only need attention to detail, but also have a big picture. You need to know the whole system in order to understand how each part affects others. This chapter discusses the data types first, followed by the indexing strategy and the database paradigm. Finally, some information about the storage engine.

After reading the statement optimization, you may need to review the chapter again. Many of the topics discussed in this chapter, especially the index, are not independent. You must be familiar with statement optimization and server tuning in order to use the index correctly.