why create index (non-primary)

[Guest]

If I create a primary key for a table, in vba I could use the seek command
and it speeds up record search. This I am aware of.

But what is the purpose of creating an index that is not "primary-key"? If I
am not mistaken, I couldn't use the seek command.

The only use I see is: in vba, open recordset, set index for the recordset,
then I could loop through the record in the recordset and it gives out
records in decending/asc order of key.

Wouldn't it have the same result if I create a recordset using a query
statement like: select ... from ... order by fldname asc. For my case the
table of interest has normaly a few records (never 100's).

I might overlook other uses of indexes. Please point out anything I miss.
Thanks.

 

[Allen Browne]

An index is an ordered list that allows the data engine to retrieve an
answer instantly, instead of having to scan the entire table.

To use a paper-based analogy, if you want to a non-computerized library they
would provide several indexes to the books, e.g.:
- index cards in alphabetic order by Author.
- index cards in alphabetic order by Title.

If you converted that straight into a (non-normalized) database, you would
finish up with fields:
ISBN primary key
Title Text
Author Text
The ISBN code for the book is a unique code, like a primary key.
However, if you *index* the Title field, you can instantly search by title.
If you *index* the Author field, you can instantly locate a book by author.
Without an index, the database cannot present these fields in order, and so
it has to search through all the records to find a book by Title.

Whenever you create a query and enter criteria on a field, the data engine
inside Access (called JET) automatically uses the index if you have one.
Same for any field you ask it to sort by. So, any field that is frequently
used for criteria or sorting should be indexed. The difference it makes can
be orders of magnitude. Microsoft actually bought the best index-utilization
and data-optimization engine available for PCs (named Rushmore), and put it
into Access, so although you can't see it using your indexes, it just does.

BTW, the Seek method (DAO) works only on local tables. I suggest you don't
use that, as your code will need to be re-written when you split the
database (as you are guaranteed to do if you are a serious developer).
Instead, use a SQL statement that retrieves the fewest fields and records
you need, in the order you need. Rushmore uses the indexes anyway, and the
code is simpler and easier to maintain.

 

[Dirk Goldgar]

If I create a primary key for a table, in vba I could use the seek
command and it speeds up record search. This I am aware of.

But what is the purpose of creating an index that is not
"primary-key"? If I am not mistaken, I couldn't use the seek command.

The only use I see is: in vba, open recordset, set index for the
recordset, then I could loop through the record in the recordset and
it gives out records in decending/asc order of key.

Wouldn't it have the same result if I create a recordset using a query
statement like: select ... from ... order by fldname asc. For my case
the table of interest has normaly a few records (never 100's).

I might overlook other uses of indexes. Please point out anything I
miss. Thanks.

The purpose is to help the database engine execute joins and apply query
criteria efficiently. Indexing makes a huge difference in query
performance. For example, if you have a table with 500,000 records in
it, and you want to find the 20 records in it WHERE State = 'NJ', then
if the [State] field is not indexed the database engine has to read
every record in the table to find those 20 records. If there's an index
on the [State] field, the database engine can use the index to pull just
those records out without reading any others. Similarly, if you join
this table with another, using [State] as the join field, then the
database can match up the records much more efficiently by working
through the index.