primary key or index?

  • Thread starter Thread starter Garret
  • Start date Start date
G

Garret

I have a question that doesn't apply exactly to anything I am building,
but I think I should know it. What is the point of indexes? I can see
why primary keys have indexes - so that each record is unique in one or
more fields. Other fields that are characteristics of the primary key
I can see logically as not having any uniqueness to them. Yet there
lies third case of having an Index with duplicates OK. Where would
this be used? Everywhere I look it just says indexes speed up searches
and slow down updates - which doesn't mean anything to me. Can anyone
who knows about this provide a little info for me? Much Thanks.
 
Let's assume you have a Customer table. As you've noted, you'd want a
Primary Key on the CustomerNumber, to ensure that you don't get any
duplicate customers. However, if you're constantly doing extracts of
customers by state (or province), you'd want an Index with duplicates OK on
the State (or Province) field. That will make the retrieval faster:
otherwise, it's going to have to read every record, check whether it's the
appropriate state (or province) and move on to the next record.
 
Ohhh, I see. This might save me alot of time. It would be of great
help if you could explain how the process works with an index then, if
you say w/o an index searches through every record. (give me an
example or something)
 
Realistically, from your perspective, there's no difference. You write the
same queries whether or not there are indexes. Under the covers, the
Rushmore Technology that's part of Jet determines the best way to run the
query.

Think of it along the lines of your Customer table having 10 rows in it,
with an average row size of 240 bytes. Since it has far fewer fields in it,
the index will be far smaller than the Customer table. Reading the index to
determine the rows that are relevant will have to do far fewer reads than
having to read the entire table.
 
This is astonishing. I just may be able to use index fields rather
than create whole tables.
In our discussion here:
http://groups-beta.google.com/group...c5034/d24736fd3964f2c0?hl=en#d24736fd3964f2c0
I said I needed the lookup table for searching purposes. I wanted to
find individual records based on one GageType, so that it wont have to
search through every record, so thats why the main reason the GageType
table exists. If the computer acknowledges that indexes are there, and
only checks the indexed ones for criteria, will I be essentially doing
the same thing, except without a whole table?
 
P.S. I'm thinking of searching by checking the Parent GageType for its
child Gages. I don't know how to do this but is there a way?
 
The index does not replace the need for tables.

The tblGageType table exists so that you don't have to store data
redundantly.

You store only the GageType value in your main table. You store the
description, and whatever other pieces of information are relevant to
GageType in tblGageType. You join the two tables when, for example, you want
to print the details from your main tble along with a description of each
GageType.

By creating a relationship between your main table and tblGageType, you
already have an index on the GageType field in your main table.
 
You store only the GageType value in your main table.
Click to expand...

What do you mean by this? I thought we had decided I was going to use
a combobox to store values, not a GageType field in my main table. In
my tblGageTypes, I only have GageType(PK) and GageTypeDescription. The
Gages (main table) has TypeofGage, so that the Gages don't have child
records of the GageTypes, only visa versa being the case. Visually,
this means that the tblGageType's GageType field has the + that breaks
off so you can see which fields in tblGage have the same TypeofGage,
but in the tblGage there are no + signs. I found that having
TypeofGage, since it is a different name than GageTypes, makes it work
this way (which is what I want). If the fields in both tables were
called GageType, then both tables would have the +.
Sorry for my bad explanation.
You store the description, and whatever other pieces of information are
relevant to GageType in tblGageType. You join the two tables when, for > example, you want to print the details from your main tble along with a > description of each GageType.

By creating a relationship between your main table and tblGageType, you
already have an index on the GageType field in your main table.
Click to expand...

I created the relationship, and noticed the index on TypeofGage. I
noticed it on all my other tables with relationships, but I deleted all
the indexes because I didn't see them necessary. Which ones should I
have indexes on?
 
You know how you can search a record for criteria. I want to be able
to search a record's child records. Example: In the tblGageTypes, I
have a field called FLSHPLG. All the fields in tblGages with the
TypeofGage, FLSHPLG, are children of this record. In a form (or
anywhere), how would I check the FLSHPLG record to determine what
records it holds.
I do this because if I searched through the tblGages for all with the
TypeofGage being FLSHPLG, then I would find them, but the computer has
to search through every single record (could be thousands). Out of the
20 or so GageTypes, this narrows down the search to 1/20th of the time
and effort.
 
You know how you can search a record for criteria. I want to be able
to search a record's child records. Example: In the tblGageTypes, I
have a field called FLSHPLG. All the fields in tblGages with the
TypeofGage, FLSHPLG, are children of this record. In a form (or
anywhere), how would I check the FLSHPLG record to determine what
records it holds.
I do this because if I searched through the tblGages for all with the
TypeofGage being FLSHPLG, then I would find them, but the computer has
to search through every single record (could be thousands). Out of the
20 or so GageTypes, this narrows down the search to 1/20th of the time
and effort.
 
What do you mean by "check the FLSHPLG record"?

From your description, there's no such thing as a "FLSHPLG record". You've
got two tables. tblGageTypes has 20 records in it, each one having a
different value for FLSHPLG. tblGages has n records in it, each one of which
has a specific value in the FLSHPLG field.

If you want your form to only show those records in tblGages that have a
specific value in the FLSHPLG field, you can create a filter to limit which
records are shown (or you can base your form on a query that limits which
records are returned from the table)
 
Oops! I'm sorry, when I first said FLSHPLG, I meant record. FLSHPLG
is one of the 20 records in the GageType field.
 
Douglas said:
What do you mean by "check the FLSHPLG record"?

From your description, there's no such thing as a "FLSHPLG record". You've
got two tables. tblGageTypes has 20 records in it, each one having a
different value for FLSHPLG. tblGages has n records in it, each one of which
has a specific value in the FLSHPLG field.
Click to expand...

Ohhh no no no. FLSHPLG is one of the 20 types of GageTypes, not a
field. It is correct to say the "FLSHPLG record", not the "FLSHPLG
field". The only fields in tblGageTypes are GageType, and
GageDesc(ription). FLSHPLG is a GageType.
 
Another question regarding indexes (maybe). If I have the code below
inside a command button which finds records based on criteria in
[txtSearch] and [fmeSearchby], does it make the searches go faster if
the ToolDesc and ToolType fields are indexed? (ToolNo is the PK, so
already indexed).

'1 if by ToolNo, 2 if by ToolDesc, 3 if by ToolType
Select Case [fmeSearchby]
Case 1:
[lstSearch].RowSource = "SELECT [tblTools].[ToolNo],
[tblTools].[ToolDesc], [tblTools].[ToolType], [tblTools].[QtyStock]
FROM [tblTools] WHERE ((([tblTools].[ToolNo]) Like " & "'" &
[txtSearch] & "'" & ")) ORDER BY [tblTools].[ToolNo];"
Case 2:
[lstSearch].RowSource = "SELECT [tblTools].[ToolNo],
[tblTools].[ToolDesc], [tblTools].[ToolType], [tblTools].[QtyStock]
FROM [tblTools] WHERE ((([tblTools].[ToolDesc]) Like " & "'" &
[txtSearch] & "'" & ")) ORDER BY [tblTools].[ToolNo];"
Case 3:
[lstSearch].RowSource = "SELECT [tblTools].[ToolNo],
[tblTools].[ToolDesc], [tblTools].[ToolType], [tblTools].[QtyStock]
FROM [tblTools] WHERE ((([tblTools].[ToolType]) Like " & "'" &
[txtSearch] & "'" & ")) ORDER BY [tblTools].[ToolNo];"
End Select
 
Yes, having indexes on ToolDesc and ToolType should make the searches
faster. Replacing Like with = likely will as well (you're not providing
wildcard characters, so there's no point in using Like)

Of course, the more indexes you have, the longer each insert/update will
take.
 
Douglas said:
Yes, having indexes on ToolDesc and ToolType should make the searches
faster. Replacing Like with = likely will as well (you're not providing
wildcard characters, so there's no point in using Like)
Click to expand...

Actually, I want users to be able to search by part of the record name.
There are sets of tools that start with G12..., G13..., etc, so the
user can type "G12*" in the box and find all the records that contain
this.
Of course, the more indexes you have, the longer each insert/update will
take.
Click to expand...

It says in many places that Indexes are best used on fields where the
data in them will be different, or the search speed isn't significant.
There are only 10 or so different Tool Types, (there might be 200 of
each record or more of each Type), so would it be useful here still?

Back to kind of what I was talking about before regarding this also - I
was told that its better that I should make a separate table for the
values of the GageType field in the tblGages (and call this table,
tblGageTypes). I was told this for the reason that its faster to
search when wanting to know all the Gages of a certain GageType, to do
a search in the tblGageType for all the child records of one record.
Is this true? If so, how would I do this search (searching the parent
GageType record for its child Gages)
 
Garret said:
Actually, I want users to be able to search by part of the record name.
There are sets of tools that start with G12..., G13..., etc, so the
user can type "G12*" in the box and find all the records that contain
this.
Click to expand...

Aah, that makes sense.

I believe the indexes will still help with G12*, but not with *12*.
It says in many places that Indexes are best used on fields where the
data in them will be different, or the search speed isn't significant.
There are only 10 or so different Tool Types, (there might be 200 of
each record or more of each Type), so would it be useful here still?
Click to expand...

Since you've got a relationship between the tables on ToolType (unless
you've changed your model), you already have an index. Foreign keys in
relationships automatically get indexed.
Back to kind of what I was talking about before regarding this also - I
was told that its better that I should make a separate table for the
values of the GageType field in the tblGages (and call this table,
tblGageTypes). I was told this for the reason that its faster to
search when wanting to know all the Gages of a certain GageType, to do
a search in the tblGageType for all the child records of one record.
Is this true? If so, how would I do this search (searching the parent
GageType record for its child Gages)
Click to expand...

See above.
 
Since you've got a relationship between the tables on ToolType (unless
you've changed your model), you already have an index. Foreign keys in
relationships automatically get indexed.
Click to expand...

Right now I have tables where there is no Index on the related field.
I even destroyed the relationships and rebuilt them to see again, but
no Index was created.
See above.
Click to expand...

I don't follow...this is an issue I've been having trouble with for
some time.
 
The index doesn't show up in the list of indexes, but it's there (and it
contributes to the maximum of 32 indexes per table that Access has)

As a test, I created 2 tables:

Parent
Id (PK)
Desc

Child
Id (PK)
Desc
Parent_Id

I added the two primary keys, and made sure that there were no other indexes
showing in the indexes view.

I then wrote a routine that displays the indexes (and what's in them):

Sub ShowIndexes(TableName As String)
On Error GoTo Err_ShowIndexes

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim idxCurr As DAO.Index
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs(TableName)
For Each idxCurr In tdfCurr.Indexes
Debug.Print idxCurr.Name & ":"
For Each fldCurr In idxCurr.Fields
Debug.Print " " & fldCurr.Name
Next fldCurr
Debug.Print
Next idxCurr

End_ShowIndexes:
Set tdfCurr = Nothing
Set dbCurr = Nothing
Exit Sub

Err_ShowIndexes:
MsgBox Err.Number & ": " & Err.Description, _
vbOKOnly + vbCritical
Resume End_ShowIndexes

End Sub

Here's the results of running it in the Immediate window before creating the
relationship:

Call ShowIndexes("Child")
PrimaryKey:
Id

I created a relationship between the two tables.

Here's the results of running it in the Immediate window after creating the
relationship:

Call ShowIndexes("Child")
ParentChild:
ParentId

PrimaryKey:
Id

However, if I view the indexes on table Child, all I see is PrimaryKey (the
one I created)
 
Back
Top