What's the benefit of indexing?

[JethroUK©]

i sort of loosly understand that indexed field are basically 'catalogued' -

the trade being an increase in search speed against an overhead slow down of
building (re-building) the catalogue

i was inclined to index some fields because i actually do a lot of searching
by virtue of the fact that nearly everyone of my text fields (30+) is bound
to a combo, which is in turn governed by a Select statment, which is inturn
refreshed (searched) every single time i change records (form-current event)

as is:

non of the fields are indexed, the refresh is negligable - but there are
still currently under 200 records to search (which might explain why)

tedious i know but i'm thinking for future - i spose what i'm really asking
is, whether there any specific statistics relating to index v non-index, per
record, pro-rata

 

[Guest]

One of the nice things about indexes is that they can be added at any point.
Typically, you add them when you think they'll improve performance. If you're
not seeing and performance hit by not having them, defer it until you see a
slow down.

My $.02,
Barry

 

[Allen Browne]

You won't notice any difference with just a couple of hundred records. Once
you get thousands you start to notice the difference; tens of thousands the
difference can become an order of magnitude; hundreds of thousands of
records the difference can be whether the database is useable or not.

If your combos are looking up another table that has relationships with
referential integrity turned on, Access has already created hidden indexes
on these fields to manage the relationships. Also if your field name ends in
ID, Num, Code, etc, Access will have automatically created indexes, unless
you deleted the entry under:
Tools | Options | Tables/Queries | AutoIndex on create/import.

You cannot instruct JET to use indexes, but it makes very intelligent
decisions about which indexes to use (Rushmore.) In general, it will use an
index if available. It cannot use the index if your criteria uses a leading
wildcard, e.g.:
Like "*Jethro"

The index probably cannot be used on expressions that include VBA functions
either, e.g.:
WHERE Nz([Address], "") = ""
probably won't use the index, whereas:
WHERE [Address] = "" OR [Address] Is Null
can use the index.
Similarly:
WHERE Month([OrderDate] = 1) AND Year([OrderDate) = 2006
cannot use the index on the OrderDate field, whereas this can:
WHERE OrderDate Between #1/1/2006# And #1/31/2006#

As a general guide, index those fields that are regularly used for searching
(criteria) or sorting, unless the field is a foreign key (since Access has a
hidden index on that.)

Conventional wisdom is only use indexes on fields that contain many values,
e.g. do not index a Yes/No field. You need to run your own timing test on
this though. It can be worth indexing a yes/no field that is constantly used
for criteria. We found JET 3.5x was an order of magnitude faster at
retrieving active clients (Inactive field is No) if this yes/no field was
indexed.

 

[Guest]

You cannot instruct JET to use indexes

Just think of how much more fun we'd have if Access could use hints like
Oracle!
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

You won't notice any difference with just a couple of hundred records. Once
you get thousands you start to notice the difference; tens of thousands the
difference can become an order of magnitude; hundreds of thousands of
records the difference can be whether the database is useable or not.

If your combos are looking up another table that has relationships with
referential integrity turned on, Access has already created hidden indexes
on these fields to manage the relationships. Also if your field name ends in
ID, Num, Code, etc, Access will have automatically created indexes, unless
you deleted the entry under:
Tools | Options | Tables/Queries | AutoIndex on create/import.

You cannot instruct JET to use indexes, but it makes very intelligent
decisions about which indexes to use (Rushmore.) In general, it will use an
index if available. It cannot use the index if your criteria uses a leading
wildcard, e.g.:
Like "*Jethro"

The index probably cannot be used on expressions that include VBA functions
either, e.g.:
WHERE Nz([Address], "") = ""
probably won't use the index, whereas:
WHERE [Address] = "" OR [Address] Is Null
can use the index.
Similarly:
WHERE Month([OrderDate] = 1) AND Year([OrderDate) = 2006
cannot use the index on the OrderDate field, whereas this can:
WHERE OrderDate Between #1/1/2006# And #1/31/2006#

As a general guide, index those fields that are regularly used for searching
(criteria) or sorting, unless the field is a foreign key (since Access has a
hidden index on that.)

Conventional wisdom is only use indexes on fields that contain many values,
e.g. do not index a Yes/No field. You need to run your own timing test on
this though. It can be worth indexing a yes/no field that is constantly used
for criteria. We found JET 3.5x was an order of magnitude faster at
retrieving active clients (Inactive field is No) if this yes/no field was
indexed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

i sort of loosly understand that indexed field are basically 'catalogued' -

the trade being an increase in search speed against an overhead slow down
of
building (re-building) the catalogue

i was inclined to index some fields because i actually do a lot of
searching
by virtue of the fact that nearly everyone of my text fields (30+) is
bound
to a combo, which is in turn governed by a Select statment, which is
inturn
refreshed (searched) every single time i change records (form-current
event)

as is:

non of the fields are indexed, the refresh is negligable - but there are
still currently under 200 records to search (which might explain why)

tedious i know but i'm thinking for future - i spose what i'm really
asking
is, whether there any specific statistics relating to index v non-index,
per
record, pro-rata