Indexing Fields in Microsoft Access Tables for Optimal Database Performance

Indexing is one of those features that is almost never used correcly by new Access users. You can get away without indexing anything in a small database. However as your database grows, you're going to want it to keep things running smoothly. As your database gets bigger and bigger, it's going to get slower and slower if your tables aren't properly indexed.

LEARN MORE:

The first use of Indexing is to prevent duplicate values. Obviously you want to make sure your Primary Key is indexed so that you don't have two customers with CustomerID 3, for example. Access will automatically index an AutoNumber field.

You can use Yes (Duplicates OK) to have Access index the field for the purposes of speeding up searches and sorts. For example, let's say you do a lot of searching for customers based on their last name. You may want to index that field, but allow duplicates. You still want to allow multiple people named "Smith," but because you search on it a lot, you want to index it.

See, when you enter records into a table, the data is stored in no particular order. So if you want to sort this list, it's slow. If you want to search for a single name, Access has to start at the top, and run through all the records until it finds what you're looking for. Imagine how difficult it would be to find one person in an unsorted phone book (remember those?)

But when you index a field, Access creates a separate index table (it's hidden, you don't see it) that it manages. It's sorted by that field, and this greatly speeds up searches and sorts based on that field.

Don't index too many fields, though. Doing so will increase the size of your database needlessly. And, indexing slows down updates and appends because Access has to rebuild that index table every time you make changes. In fact, if you do more data entry and editing than lookups, you may want to consider not indexing any fields. If you have a dozen people doing data entry all day, and you only run reports on that information once a month, go easy on the indexes. It's all situational based on the needs of your business.

You can index Short Text, Long Text, Number, Date/Time, AutoNumber, Yes/No, and Hyperlink fields. You cannot index any of the field types that you shouldn't be using anyways: OLE Object, Calculated, Attachment. In older versions of Access you didn't used to be able to index Memo fields, which are now called Long Text fields, or Hyperlink fields. Honestly, I almost never index Long Text as that's going to be a real performance hit on your database.

By default, Access will automatically index fields that end in ID, key, code, or num. You can disable this under File - Access Options - Object Designers - AutoIndex on Import/Create.

The only time I ever come in here is to create something called a Composite Key which is an index based on two fields, such as not having the same product twice on an order.

If you want to learn more about indexing, I cover it in my Access Beginner Level 4 class. I walk you through all the different fields in my database and explain which ones I index and why. I also cover a whole bunch of other field properties, compact & repair, backing up your database, and lots more.

LEARN MORE:

RECOMMENDED COURSES:

LINKS:

BECOME A MEMBER:

ADDITIONAL RESOURCES:

KEYWORDS
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, index, indexing, indexed, indices, primary key, foreign key, yes (no duplicates), yes (duplicates ok), AutoIndex, How to Create Index for a Table, Creating Indexes, Indexing a Field, unique index, What is indexing, How do you use an index

QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[tigranhayrapetyan]

Thank you, Richard! I've maken some columns in my databases for multi-table search form and it becomes extremly fast! 1 second instead of 15-30.

The tutorial was very helpful, thank you again 😊

[ryeaton]

Computer science vids from you: interested.

[CStrieker]

What would happen if you changed the indexing of a field to No Duplicates, but there were already duplicates in the field?

[rabidfollower]

My experience is that indexing has become less and less crucial when PCs have become faster and faster. Nowadays when a query is slow, I suspect poor query design or table design before I look into poor indexing. I mainly rely on automatically-created indices such as the ones for primary fields. And I can't remember the last time I had to manually create an index myself.

[froggert]

"You cannot index any of the field types that you shouldn't be using anyways: OLE Object, Calculated, Attachment." could you explain how one is supposed to deposit a pile of photos that will automatically adjust to the size of the report without creating duplicates?

[chess]

how does duplicate(not ok) helps reduce I/O cost.
isn't it making a index for a single data?
or
it is just used during data entry to have no duplicate values? if this is the case then how is this index(not ok) helping to reduce searching time(i/o cost)??

ALSO

using duplicate ok in auto number?
n
is duplicate ok working as primary key??(same as above qstn)

[arodtv]

Computer science! Put my mane on that list.

[stephenbeswick]

Yes please I would be very interested in learning about science yes please Richard?

[colinhursell]

I would be interested in computer.science videos.