Table Index Construction

[lauren quantrell]

In a table design, properties window there is the Indexes/Keys tab.
I want to create a few indexes.

Row myID is the PK.
I also want indexes on rows myOne, myTwo, myThree.

In the selected index window: PK_tblName I see myID.
Do I put myOne, myTwo, myThree under that in the same index or do I
create three new indexes for the table?

Maybe this is DB Design 101 but it's not intuitive.

Any help is appreciated.
lq

[Bob Quintal]

"lauren quantrell" <la************ *@hotmail.com> wrote in
news:11******** ************@f1 4g2000cwb.googl egroups.com:

In a table design, properties window there is the Indexes/Keys
tab. I want to create a few indexes.

Row myID is the PK.
I also want indexes on rows myOne, myTwo, myThree.

In the selected index window: PK_tblName I see myID.
Do I put myOne, myTwo, myThree under that in the same index or
do I create three new indexes for the table?

Maybe this is DB Design 101 but it's not intuitive.

Any help is appreciated.
lq

If you put myOne, myTwo and myThree under the same index name,
you have one index that sorts on the Primary Key, and the myOne
within the primary key, myTwo within myOne etc, and the whole
thing becomes the primary key. I doubt that this is what you
want.

Each index should be given a separate name, but may contain
multiple columns. So you could build indixes for myOne, myTwo,
and another index that sorts by myThree and myOne if it makes
sense to order that way.

Note that you don't need to create many manual indixes as Access
automatically indexes fields related in the relationships
window.
--
Bob Quintal

PA is y I've altered my email address.

[lylefair]

From:Microsoft® Jet Database Engine Programmer's Guide

**** begin quote ****
Use Indexes Cautiously
Although having indexes can reduce the time it takes to retrieve data,
they always carry a cost in maintenance and concurrency issues. When
should a field be indexed? There is no strict answer for this because
it depends on the type of application.

The first guideline is that fields containing highly duplicated data
should not be indexed (for example, fields with the Yes/No data type,
and fields that represent gender, state abbreviations, or country
codes).

The second guideline is that fields should not be indexed simply to
force Rushmore to use more than one index. An example of this would be
indexing a field called City and a field called PostalCode in a
customer table when the application is always going to be using both
fields for retrieval purposes. In this instance, the PostalCode field
is going to be the most unique index and would return a result set
faster if the City field was not indexed. Because Rushmore doesn't
need to use the index on the City field, omitting the index on the City
field will reduce overall disk I/O. Of course, if both values were not
always being entered and they were used alternatively and equally, then
having an index on both fields would probably be advantageous. Rushmore
is best utilized on combined indexes when they are required to generate
a unique result set.

It is also important to remember that indexes create concurrency
issues. One index page represents many data pages. Therefore, modifying
an index page can cause users with data on an entirely different data
page to be locked out when trying to update the indexed field.

To see this behavior, open the Northwind database in Microsoft Access
97 and turn on pessimistic locking by clicking Edited Record on the
Advanced tab of the Options dialog box (Tools menu). Update a value in
one indexed field in the Customers table, but don't move to the next
record. On another workstation, open the Customers table and try to
edit another value in the same indexed field that the other workstation
is editing. Next, try updating a value in a non-indexed field in the
Customers table. What will become evident is that substantially more
records of data are locked when you try to update a value in an indexed
field than when you try to update a value in a non-indexed field.
**** end quote ****

[David W. Fenton]

"lylefair" <ly******@yahoo .ca> wrote in
news:11******** **************@ z14g2000cwz.goo glegroups.com:

From:Microsoft© Jet Database Engine Programmer's Guide

**** begin quote ****
[snip]
The first guideline is that fields containing highly duplicated
data should not be indexed (for example, fields with the Yes/No
data type, and fields that represent gender, state abbreviations,
or country codes).

I know this is the orthodoxy on this issue, but I don't follow it.

I've found in plenty of cases that sparsely-populated indexes (i.e.,
indexes with a small number of unique values) can massively speed up
selects that involve those fields. This includes Boolean fields, but
the most common case where I've encountered this is in one of the
fields I use for stamping records with data about who edited it. My
general approach is to have Created, Updated and UpdatedBy fields.
The first two are dates (with Default=Date()) , and the last a text
field that I populate from CurrentUser() or from the Windows user
name (depends on the app). UpdatedBy never has a large number of
unique values as none of my apps are being used by more than a
couple dozen unique users.

So, by the orthodox rule, you'd not index such a field.

But when I *don't* index it, my routines to retrieve and sort and
count for the user stats take much, much longer. So, I put in the
indexes, despite the logic behind the orthodox rule not to index
such fields.

And I've seen the same thing on Boolean fields.

It does seem to me, though, that fields that have lots of Null
values are not speeded up nearly as much as those where all records
have a value.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc