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:
599cd.com/Indexing
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 may also want to indexing it for any field you want to uniquely identify a record with. For example, I index email address as no duplicates. There can be one and only one customer with [e-mail], for example. Some other companies may index phone number to uniquely identify customers.
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:
599cd.com/Indexing
RECOMMENDED COURSES:
Access Beginner 4: 599cd.com/ACB4
LINKS:
Prevent Duplicates: 599cd.com/CompositeKey
Multi-Field Index: 599cd.com/MultiFieldIndex
Evil Access Stuff: 599cd.com/EvilAccessStuff
BECOME A MEMBER:
TH-cam: / @599cd
or My Site: 599cd.com/THMember
ADDITIONAL RESOURCES:
FREE Access Beginner Level 1: 599cd.com/Free1
$1 Access Level 2: 599cd.com/1Dollar
Donate to my Tip Jar: 599cd.com/TipJar
Get on my Mailing List: 599cd.com/YTML
Contact Me: 599cd.com/Contact
TechHelp: 599cd.com/TechHelp
Consulting Help: 599cd.com/DevNet
Twitter: / learningaccess
en.wikipedia.org/wiki/Microso...
products.office.com/en-us/access
microsoft.com/en-us/microsoft...
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.

[@tigranhayrapetyan8357]

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 😊

[@599CD]

You're welcome. Indexing def speeds things up! Just remember not to OVERUSE them. Too many indexes in a table can slow down updates, additions, and deletions because those indexes have to be updated. It's a balance.

[@tigranhayrapetyan8357]

@@599CD yeah, thank you for the comment.
In my case I need only search in tables, not to add or update something.
Thank you again for your awesome work on this channel🙂

[@annagevorkian5615]

Interested, can’t wait!!!

[@599CD]

For what?

[@ryeaton]

Computer science vids from you: interested.

[@599CD]

Kewl

[@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.

[@599CD]

Yeah, indexing is usually something I do well after the database is up and running.

[@alializadeh8195]

Thanks

[@599CD]

Welcome

[@CStrieker]

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

[@rabidfollower]

It would say "can't create index, duplicates found" or something to that effect.

[@599CD]

What ^ he said.

[@arodtv8582]

Computer science! Put my mane on that list.

[@599CD]

Roger that

[@chess86362]

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)

[@599CD]

599cd.com/ask

[@stephenbeswick2163]

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

[@599CD]

Sweeeeet

[@froggert119]

"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?

[@colinhursell9176]

I would be interested in computer.science videos.

[@599CD]

Awesome

[@cruzinsweetsntreats]

Optimus Prime indexing.... 🤔🤫😁

[@599CD]

Megatron deleting...

[@cruzinsweetsntreats]

@@599CD exactly 💯👍

[@nathanwillett6918]

youre referring to REI arent you

[@599CD]

What's that?