when to use indexes

[WindAndWaves]

Hi Everyone

Is there anyone who has some sound rules of thumb for using indexes.

I see that, for example, access automatically adds them to linked tables,
but I feel, they are probably of more use in text fields (for sorting
purposes), etc....

Keen to hear your ideas.

Thank you (or as we say: CHEERS)


---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004

[Lyle Fairfield]

"WindAndWaves" <ac****@ngaru.com> wrote in news:ScOwc.1058$GB4.35933
@news.xtra.co.nz:

Is there anyone who has some sound rules of thumb for using indexes.

Pretty old advice from
Microsoft® Jet Database Engine Programmer’s Guide!
The last paragraph (is it still valid?) may describe an issue which can be
overlooked.

**** 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.
**** quote ****

--
Lyle
(for e-mail refer to http://ffdba.com/)

[WindAndWaves]

Ok,thanks for that, so does that mean that if someone is adding information
to a table with an index and someone is editing another record in the table
at the same time that then there will be concurrency issues????? Does that
still apply to Access 2003?????

I gather : USE INDEXES WHEN:::

1. if the data in the field is different for each record
2. if you are likely to search or sort on the field
3. if there is another field X in the record that has more of the
characteristics above then field Y then don't bother creating a second
index.

How about date fields. They satisfy point 1 and point 2.

Is there any reason to index numeric fields????

"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...

"WindAndWaves" <ac****@ngaru.com> wrote in news:ScOwc.1058$GB4.35933
@news.xtra.co.nz:

Is there anyone who has some sound rules of thumb for using indexes.
Pretty old advice from
Microsoft® Jet Database Engine Programmer's Guide!
The last paragraph (is it still valid?) may describe an issue which can be
overlooked.

**** 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.
**** quote ****

--
Lyle
(for e-mail refer to http://ffdba.com/)

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004

[Tom van Stiphout]

On Mon, 7 Jun 2004 12:18:24 +1200, "WindAndWaves" <ac****@ngaru.com>
wrote:

One additional reason for an index, and in particular a unique index:
to enforce a business rule stating that the values in this column
should be unique.

Data type has very little to do with whether or not you should apply
an index.

-Tom.

Ok,thanks for that, so does that mean that if someone is adding information
to a table with an index and someone is editing another record in the table
at the same time that then there will be concurrency issues????? Does that
still apply to Access 2003?????

I gather : USE INDEXES WHEN:::

1. if the data in the field is different for each record
2. if you are likely to search or sort on the field
3. if there is another field X in the record that has more of the
characteristics above then field Y then don't bother creating a second
index.

How about date fields. They satisfy point 1 and point 2.

Is there any reason to index numeric fields????

"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...

"WindAndWaves" <ac****@ngaru.com> wrote in news:ScOwc.1058$GB4.35933
@news.xtra.co.nz:

> Is there anyone who has some sound rules of thumb for using indexes.

Pretty old advice from
Microsoft® Jet Database Engine Programmer's Guide!
The last paragraph (is it still valid?) may describe an issue which can be
overlooked.

**** 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.
**** quote ****

--
Lyle
(for e-mail refer to http://ffdba.com/)

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004

[WindAndWaves]

yeah, definitely Tom (inderdaad).
---------------A----------------------
so you are saying that even for a field with a byte as fieldsize it may pay
to have an index?

Does the length of the table have any influence on whether or not you should
use an index????
---------------B----------------------
I often use indexes with multiple fields.

Where you need to have unique combinations.

For example, a table with the following fields:
1. contactID
2. contactGroupID

in such a table you store combinations of contacts and the groups they
belong too. To stop double entries, you make sure that you do not enter a
contact twice for one group.

Thanks for your point.

"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:06********************************@4ax.com...

On Mon, 7 Jun 2004 12:18:24 +1200, "WindAndWaves" <ac****@ngaru.com>
wrote:

One additional reason for an index, and in particular a unique index:
to enforce a business rule stating that the values in this column
should be unique.

Data type has very little to do with whether or not you should apply
an index.

-Tom.

Ok,thanks for that, so does that mean that if someone is adding informationto a table with an index and someone is editing another record in the tableat the same time that then there will be concurrency issues????? Does thatstill apply to Access 2003?????

I gather : USE INDEXES WHEN:::

1. if the data in the field is different for each record
2. if you are likely to search or sort on the field
3. if there is another field X in the record that has more of the
characteristics above then field Y then don't bother creating a second
index.

How about date fields. They satisfy point 1 and point 2.

Is there any reason to index numeric fields????

"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...

"WindAndWaves" <ac****@ngaru.com> wrote in news:ScOwc.1058$GB4.35933
@news.xtra.co.nz:

> Is there anyone who has some sound rules of thumb for using indexes.

Pretty old advice from
Microsoft® Jet Database Engine Programmer's Guide!
The last paragraph (is it still valid?) may describe an issue which can be overlooked.

**** 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.
**** quote ****

--
Lyle
(for e-mail refer to http://ffdba.com/)

---
Please immediately let us know (by phone or return email) if (a) this

emailcontains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004