Indexes (Indices?): The Beginner's Perspective

[JPB]

I am trying to understand the meaning and use of indices in Access tables.
I've not worked with them before, so my perspective is that of the beginner.

I've looked at descriptions of what an index is in the help files, and I
think I understand the analogy to an index in a book. I can see how such a
thing would be useful for sorting, searching, and joining.

I've also looked into how they are are created. I can do so in the Table
Design View, and I can do so via a query using the CREATE INDEX command. So
far, so good.

The thing is that it seems kind of anti-climactic to have created indices,
since I can't see how one makes any use of them. Having created an index on
a table in a sample data set, I don't see any options involving the index in
any of the places where I initiate searching, sorting, or define querries.
All these things look the same and appear to act the same with our without
the index.

So, what's the point? Assuming that the index is (in some circumstances)
actually useful, either I am missing something I have to do in order to take
advantage of it, or it's a kind of black-box that, once created, sits in the
background where Access can follow some unspecified and uncontrolled (at
least by an ordinary user) rules for using it when searching, sorting, or
joining.

I would be quite grateful if one or more of y'all would be willing to clear
up my evident confusion.

JPB

 

[mnature]

Tables are stored on your hard drive as a file. Disk access is typically
slow. To sequentially search through a file, record-by-record, requires
numerous disk accesses and is very inefficient.

An index file (used by setting an index in your table), is a way to provide
direct random access to data in your database file.

To view the indexes for a given table, open the table in design view, and
then choose Indexes from the View menu.

It is a black-box, because generally you will not be sitting and watching
all the disk-accesses as they occur, to see if there are fewer when there is
an index set in a table.

 

[JPB]

So, I have the impression that having created the index there's nothing
further for me to do with it. It seems like I don't have to specify anything
referring to the index when I create a query using the indexed fields or
search them for a value or values or attempt to sort on them.

Is that the idea?

 

[mnature]

As far as I know, they just quietly do their job in the background.

 

[Tim Ferguson]

The thing is that it seems kind of anti-climactic to have created
indices, since I can't see how one makes any use of them. Having
created an index on a table in a sample data set, I don't see any
options involving the index in any of the places where I initiate
searching, sorting, or define querries. All these things look the
same and appear to act the same with our without the index.

.... until you get out your stopwatch.

A bit of history: once upon a time there were textfile managers like
dBase, FoxPro, Paradox & co and these things needed a lot of nurturing
like "use this index to find a record". Without that manual direction,
you just had to wait while each record in the file was read sequentially
(in whatever order that happened to be) to find whatever criterion had
been set.

Since my grandfather's day, however, there have been many advances in
database technology. The introduction of the Relation idiom, for example;
and the idea of forcing all access to records via a single software
engine and so on. Access in particular (strictly speaking, the Jet
database engine) uses a technology called Rushmore that optimises queries
and look ups by using whatever indexes are available. As a last resort
there is always the sequential search but only if there is no appropriate
index to help. It is of course the developers' or designer's job to
anticipate when or where an index will be helpful -- to date the engine
software cannot guess ahead of time whether a field like MgtColourSetting
will be frequently searched and sorted or not. The bottom line is that
you do need to indicate where you want indexes created, but the engine
gets to decide how useful they are. It would be really neat if there were
some kind of feedback (and in real DBMSs there is) but, AFAICT, it's all
down to blind faith in Access.

If you need to confirm all this, get out your stopwatch. Create a million
records with random longs; run a query like SELECT COUNT(*) FROM MyTable
WHERE LongNum > 4E4. Then index the LongNum column and do it again.

One final thought -- I assume that you are already comfortable with the
entirely separate consideration of Unique Indexes in logical data design
and data integrity..?

Hope that helps


Tim F

 

[Jerry Whittle]

I'll add two things:

1. If checking a query to see if it runs better after something like adding
an index, run the query twice and use the second run for your measurements.
The first time a query is run, Access optimizes it and it should run faster
afterwards.

2. If you want to see if an index is actually being used, Jet can output a
file, showplan.out, that tells you how the SQL is run. Here's a couple of
links:

http://www.fmsinc.com/tpapers/faster/index.html -- paragraph 68

http://builder.com.com/5100-6388-5064388.html