Datasheet

Book VII
Chapter 1
Tuning the
Database
587
For best results, tuning indexes must be an ongoing activity. As time goes
on, the nature of the workload is bound to evolve. As it does, the best
indexes to support the current workload need to evolve, too. The database
administrator must keep track of performance and respond when it starts to
trend downward.
Another problem, which appears after a database has been in operation for
an extended period of time, might be called the tired index. A tired index is
one that no longer delivers the performance advantage that it did when it
was first applied to the database. When an index is fresh and new — whether
it’s a B+ tree index, an ISAM index, or some other kind — it has an optimal
structure. As time goes on, insertions, deletions, and updates are made to
the table that the index is associated with, and the index must adjust to
these changes. In the process of making those adjustments, the structure of
the index changes and moves away from optimality. Eventually, performance
is affected enough to be noticeable. The best solution to this problem is to
drop the index and then rebuild it. The rebuilt index once again has an opti-
mal structure.
The only downside to this solution is that the database table must be out of
service while its index is being rebuilt. The amount of time it takes to rebuild
an index depends on several things, including the speed of the processor
and the size of the table being indexed. For some databases, you may not
even experience any downside. The database engine will rebuild indexes
automatically as needed.
Tuning Queries
After your system has been running for a while, you may find that a query is
running slower than you expect. Several possible causes exist, and you have
several ways to fix the problem. Because you generally have several ways
to code a query, all producing the same result, perhaps you could recode it,
along with an appropriate change of indexes.
Sometimes, a query doesn’t run as you expect because the query optimizer
isn’t executing the plan that you expect it to. You can check on this situation
in most DBMSes by having the optimizer display the plan that it generated.
It’s quite possible that the optimizer isn’t finding the best plan. Here are
some possible causes:
✦ Some query optimizers don’t handle NULL values well. If the table you’re
querying contains NULL values in a field that appears in the WHERE
clause, this situation could be the problem.
✦ Some query optimizers don’t handle arithmetic or string expressions
well. If one of these expressions appears in the WHERE clause, the opti-
mizer may not handle it correctly.
Tuning Queries
40_9780470929964-bk07ch01.indd 58740_9780470929964-bk07ch01.indd 587 2/24/11 3:45 PM2/24/11 3:45 PM