Reactive approach to tuning

 

To perform reactive tuning, build a prototype of the proposed application without any indexes and start running some queries or build an initial set of indexes and start running the application to see what gets used and what does not. Even with a smaller database, the slow running queries will become obvious very quickly.

The reactive tuning method is also used when trying to understand and tune an existing application that is not performing up to expectations. Using the appropriate debugging and monitoring tools, which are described in the next section, the database feedback messages that will tell basically three things can be viewed:

• Any indexes the optimizer recommends for local selection

• Any temporary indexes used for a query

• The implementation method(s) that the optimizer has chosen to run the queries

If the database engine is building temporary indexes to process joins or to perform grouping and selection over permanent tables, permanent indexes should be built over the same columns to try to eliminate the temporary index creation. In some cases, a temporary index is built over a temporary table, so a permanent index will not be able to be built for those tables. You can use the optimization tools listed in the previous section to note the creation of the temporary index, the reason the temporary index was created, and the key columns in the temporary index.

 

Parent topic:

Indexing strategy