Database Reference
In-Depth Information
After you've built your database and have gathered all the notes about its
usage, it's time to start creating indexes. In this section, we cover the key
things to think about when you create indexes for each table.
Reviewing Data Access Patterns
As we've discussed, it's crucial to know how your database is being used.
Specifically, you need to know how much time is spent writing new data to
the database and updating existing data. You also need to know how much
data is being removed from the database, and about how much of the data
will be kept online at any given time. Then you need to know how much
data retrieval there will be. How much data will be queried by the appli-
cations versus ad hoc queries? Will there even be any ad hoc queries? How
often? These answers will help you produce a logical, efficient index
scheme.
From a procedural standpoint, it's a good idea to go through the data-
base table by table—often referencing the data model (for logical refer-
ence to the entities)—and ask questions of the users and application
designers about the various ways the data is used. Document their re-
sponses, and keep those notes handy when you start indexing your tables.
Be sure to include both the frequency of the queries being run and an es-
timate of how much data will be retrieved for each query.
Balancing Indexes
As we've mentioned, there is overhead in maintaining indexes. But the key
is to know how many indexes you actually need. Make sure to create in-
dexes in situations where searching and returning the data is painfully slow
or needs to be extremely quick. For example, there is no need to create an
index on a lookup table of states in the United States. The table will have
fewer than 60 records (counting states, D.C., and territories), and the en-
tire table can be searched in subseconds, no matter what. But if the lookup
table is states, counties, and cities, it might be worth creating indexes (as-
suming you don't normalize the table). Remember that no matter what the
usage is, you should create only the indexes you absolutely need so that you
can minimize index maintenance.
Search WWH ::
Custom Search