MS Index Questions

and BTW, if you have a SQL backend, then it's a whole different story, but the indexing rules still apply as well as the approach.

Jim.

How about using something like Total Access Analyzer:

http://www.fmsinc.com/microsoftaccess/bestpractices.html

I know it will let you know of issues with indexes, but I'm not sure if it will suggest fields you should index.

It will, however, let you know where fields are used, so you can then determine if/when you should be indexing. While indexing is somewhat of a fluid science, in general you should index on any field used in a Join or WHERE clause, any field used extensively in searches, etc. You can over-index as well, so consider that when you determine which to add.

Note too that indexing needs can change as your database grows. What makes sense as in index today may not make sense in 6 months, so you should periodically revisit the indexing scheme.

<<I know it will let you know of issues with indexes, but I'm not sure if it will suggest fields you should index. >>

 I don't believe it does, simply because there are no real hooks into the DB engine to get any kind of statistics.

 The only public ones are JET Showplan and ISAM stats, and even Showplan is not complete (it's doesn't show you the costing plan for any sub-queries).

 There are hooks in the dbEngine that Microsoft internally uses for development, but no one outside of Microsoft knows how to turn them on.

Jim.

Primary keys are automatically indexed so do not create additional ones.  Foreign keys are also automatically indexed if you have defined them in the Relationships window.  I don't know whether enforcing RI makes a difference.  It doesn't sound to me like your database is normalized so this point may be moot.

Access may already have created a bunch of spurious indexes for you if you left the "Auto Index on Import/Create" setting at its default value.  It arbitrarily indexes any column that ends in ID, CD, and some other values.  Best practice is to remove this automatic indexing and create your own.

If your tables are all the same, you should be able to examine one of them and that should give you a clue what you need to do for the others.  In general you should index on columns you normally join on (other than the PK/FK which already have automatic indexes) and you should also index any columns you normally search on.  It was already pointed out that you shouldn't create indexes for fields with low cardinality of values.  You could however, use compound indexes in some cases.  The database engine will use compound indexes if the search field is the first field in the index.  So, if I add employeeID as the second column in my Department index, the database engine might use the index.  To play around with this, you would need to spend a lot of time reading query plans to determine if you are having any impact so don't go here unless you have a slow query and you want to try to speed it up.

There is a downside to over indexing particularly in a database with the type of duplication you have implemented and that is each index takes space and so increases the overall size of the database file and each index takes time to maintain so as you add and update records, the indexes must also be maintained.  The data part of your tables isn't shoved around when record size changes.  The updated record is simply added to the end of the table in the first available free space.  However, indexes are managed differently and might be rebuilt on the fly if you run out of free space in a given area.  The whole point of indexes is that records are physically adjacent and so inserts and updates need to be placed where they logically would fall.

Here's some code that finds indexes in a database and records them into a table.  This code comes from an application I created to document databases.  You'll need to create the table used in the code and create a form to collect the database name and run the code.  

Sub Create_tblTableIndexes()

    Dim db As DAO.Database
    Dim ThisDB As DAO.Database
    Dim tblLoop As DAO.TableDef
    Dim fldLoop As DAO.Field
    Dim idxLoop As DAO.Index
    Dim TD1 As DAO.TableDef
    Dim QD1 As DAO.QueryDef
    Dim TempSet1 As DAO.Recordset
    Dim Position As Integer
    Dim CountIndexes As Integer
    Dim strDatabase As String

    'strDatabase = "C:\hartman\ImportDB2.mdb"
    strDatabase = Forms!frmPrintDoc!txtDBName
    
    CountIndexes = 0
    Set ThisDB = CurrentDb()
    If strDatabase = "" Then
        Set db = CurrentDb()
    Else
        Set db = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
    End If
    
    db.Containers.Refresh
    
    Set QD1 = ThisDB.QueryDefs!QdeltblTableIndexes
        QD1.Execute
    Set TD1 = ThisDB.TableDefs!tblTableIndexes
    Set TempSet1 = TD1.OpenRecordset

    ' Loop through TableDefs collection.
    For Each tblLoop In db.TableDefs
        ' Enumerate Fields collection of each
        ' TableDef object.
             
'' add error checking for 3024 - not found in collection
'' add error checking for 3110 - no permission to read msysmodules2

        On Error GoTo ErrorHandler
        For Each idxLoop In tblLoop.Indexes
            CountIndexes = CountIndexes + 1
            Forms!frmPrintDoc!txtIndexCount = CountIndexes
            Forms!frmPrintDoc!txtIndexName = tblLoop.Name
            Forms!frmPrintDoc.Repaint
        
            If Left(tblLoop.Name, 4) = "MSys" Or Left(tblLoop.Name, 1) = "z" Or Left(tblLoop.Name, 1) = "~" Then
            Else
                Position = 1
                For Each fldLoop In idxLoop.Fields
                    TempSet1.AddNew
                        TempSet1!IndexName = idxLoop.Name
                        TempSet1!Unique = idxLoop.Unique
                        TempSet1!OrdinalPosition = Position
                        TempSet1!FieldName = fldLoop.Name
                    TempSet1.Update
                    Position = Position + 1
                Next fldLoop
            End If
        Next idxLoop
    Next tblLoop

    db.Close
Exit Sub

ErrorHandler:

Select Case Err.Number
    Case 3110
        MsgBox "Open " & strDatabase & " and change the admin security to allow read for MSysModules", vbOKOnly
    Case 3043
        MsgBox "Please select a valid database", vbOKOnly
    Case 91   ' db was not opened so it cannot be closed.
        Exit Sub
    Case Else
        MsgBox Err.Number & "-" & Err.Description
End Select
End Sub

Select allOpen in new window