Some SQL statements have a reasonably constant execution time. Examples include the CREATE TABLE and GRANT statements. It does not matter under which circumstances such statements are executed; they always need a certain execution time. There is no way to reduce their execution time. However, this is not the case for all statements. The time required to process SELECT, UPDATE, and DELETE statements varies from one statement to the next. One SELECT statement might be processed in 2 seconds, while another could take minutes. The required execution time of this type of statements can indeed be influenced.
Many techniques are available for reducing the execution time of SELECT, UPDATE, and DELETE statements. These techniques range from reformulating statements to purchasing faster computers. In this book, we discuss three of them. In Chapter 19, "Designing Tables," we looked at adding redundant data, as a result of which the execution time of certain statements can be improved. This chapter describes indexes and how their presence or absence can strongly influence execution times. In Chapter 29, "Optimization of Statements," we deal with reformulating statements. Improving execution times is also known as optimization.
Note in advance: The first sections that follow do not so much cover SQL statements as provide useful background information on how SQL uses indexes.
The syntax for creating and dropping indexes is reasonably the same in most SQL products. However, there are differences in how indexes work internally and how SQL products use them. Therefore, in this chapter, we keep the explanations general so that they apply to most SQL products.