Database Reference
In-Depth Information
a database has data being written to it at very high rates, be sure to include
only the indexes that you must include to satisfy the most-used queries. In
contrast, if data is added to your database very infrequently but is con-
stantly queried, you can be more liberal with the number of indexes you
apply. Remember, though, that there is such a thing as too much of a good
thing.
If you find that your database has periods that are write-intensive fol-
lowed by periods that are read-intensive, such as an OLAP database, you
may find yourself actually creating and dropping indexes based on data
loading processes. As with anything else, your mileage may vary, but it is
often useful to drop your indexes when large data loads are occurring and
then re-create them when the data load is finished so that queries can use
them to retrieve data. On the flip side of that coin, re-creating the index
takes resources. So you must trade off the speed of the load versus the
speed of re-creating the index. Sometimes, it is better to leave the indexes
in place during the load.
Transaction Data
Way back during the requirements gathering phase, you should have been
taking note of general metrics for the system. For example, about how
many orders per day are processed? How many employees use the system
concurrently? What is the duration of the data that must be kept online—
six months? Two years? Knowing these bits of information can help deter-
mine what the usage of your database will be. The usage will help further
define the types of indexes you place on your database.
For example, in the Mountain View Music database, if we know that
90 percent of the customers use the Web interface and 70 percent of those
are returning customers, then we need to make sure that the log-in infor-
mation lookup is a speedy process. It would be unacceptable for the cus-
tomer to have to wait 90 seconds for the log-in to complete. Therefore, we
can place a priority on the indexing scheme to make sure that this query is
fast. In contrast, if we know that once a month the customer service man-
ager will pull a report of all customers who've placed an order in the past
30 days, we can place a lower priority on creating a covering index for that
report (if it performs poorly without any help). Knowing these statistics
about the usage of your database will greatly increase your ability to index
it correctly.
Search WWH ::
Custom Search