Question about indexes with a 3-column primary key

[Rick]

I have a table whose primary key consists of three columns. The
application searches the table by issuing a query that specifies one
of the three columns. (There are three different queries, and among
them they cover all three columns.)

Do I also need to add an index for each individual column to optimize
these searches?

Thanks in advance

 

['69 Camaro]

Hi, Rick.

Do I also need to add an index for each individual column to optimize
these searches?

The primary key is an index already, so you only need to create two more
indexes. Of course, the primary key should list the most commonly used
column for sorts and criteria as the first column of the primary key, and
the second most commonly used column should be second. This second column
will need its own index, as well as the third column needing its own index,
too.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[aaron.kempf]

I would tell more about your problem; I have seen when I give a single
index on 3 columns for example.. and I'm clustering by OrderID,
LineItem, SalesPerson

that you can get better performance by clustering on all three columns
and then adding 2 additional single columns.

for sure, for real

for example a clustered index on orderid, lineitem, salesperson-- if
you're only searching for salesperson it will have to scan the whole
index I think; and it will go a lot faster if you allow for the simple
index just on SalesPerson

hth

-Aaron

 

[Guest]

Probably not. First though the big question: Are the queries unacceptably
slow? How many records are we talking about both not and potentially in the
future?

To really know the answer, you'll need to create the other indexes and see
if there is a performance increase. Or you could google Showplan for a method
of seeing how Access runs queries and uses what indexes.

Remember that indexes take up space and will slow down inserting new records
plus could slow down deletes and updates of records.

 

[Rick]

Jerry, thanks for your reply. BTW my application uses a Jet database.

I did just what you suggested and googled. The results were appalling
to say the least. Why did Microsoft put the switch for an important
development feature in the registry of all places? Access 2003 is a
mature product; an option on an Access menu (Tools -> Database
Utilities perhaps) is a far more appropriate and sensible spot for it.

Anyway, I ran a query like this:
SELECT column FROM table ORDER BY column
where "column" was the FIRST of the three in the primary key.

Here's what came out:

- Inputs to Query -
Table 'run_schedule'
Using index 'PrimaryKey'
Having Indexes:
PrimaryKey 509 entries, 4 pages, 509 values
which has 3 columns, fixed, unique, primary-key, no-nulls
- End inputs to Query -

01) Scan table 'run_schedule'
Using index 'PrimaryKey'

However running the same query against either of the other two columns
gives this:

- Inputs to Query -
Table 'run_schedule'
- End inputs to Query -

01) Sort table 'run_schedule'

.... which is just what Gunny predicted.

It turns out that this particular table has far too few rows to become
a performance bottleneck, but the lesson was meaningful nevertheless;
I learned that there's a showplan feature for Jet and I learned
something about multi-column primary keys (which may come in handy
later on).

Thanks all for your help!

Rick