Indexing
By Richard Rost
4 years ago
Indexing Fields in Microsoft Access Tables
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.
Recommended Course
Links
More Info
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 [email protected], 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.
You can view all of the indexes in a table by clicking on the Indexes button on the Table Design menu. In here you can create something called a multi-field index. Let's say you routinely search or sort based on last name and first name together. Creating a multi-field index means that your database now has an index built containing these two fields together, and doesn't have to search two separate indexes. Again, this only makes sense if you've got a zillion records, but in a big database, it can greatly improve performance. See my separate video for details on multi-field indexes.
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.
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
Subscribe to Indexing
Get notifications when this page is updated
Intro In this video, we talk about indexing fields in Microsoft Access and how proper indexing can improve your database's performance. I'll explain the difference between unique and non-unique indexes, show when to use each option, discuss automatic indexing on certain field types, and cover the impact of over-indexing on your database size and performance. We'll also look at viewing and managing indexes, including multi-field and composite indexes, and talk about which field types can and cannot be indexed. This video is packed with tips for optimizing your tables in Microsoft Access.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we're going to talk about indexing: indexing fields in Microsoft Access for optimum (or optimal, whichever you prefer) database performance. Actually, I just had to look that up. I just Googled that, and there is a difference between optimum and optimal. I always just thought it was a matter of preference. Optimal is an adjective, for example, optimal performance, whereas optimum is a noun, for example, a properly indexed table is the optimum for database performance. So I learned—I try to learn something new every day, and every time I put one of these videos together, I do a little research, and I always learn some new tidbit that I didn't know before. I love sharing this stuff with you.
All right, let's talk about indexing. Indexing is one of those features that is almost never used correctly by new users. You can get away without indexing anything at all in a small database. However, as your database grows, you're going to want to keep things running smoothly. As your database gets bigger and bigger, it's going to get slower and slower, especially if your tables aren't properly indexed.
The first use of indexing is to prevent duplicate values. This happens, for example, with an autonumber. Obviously, you want to make sure your primary key is indexed so you don't have two customers with CustomerID 3, for example. You may also want to use indexing for any field you want to uniquely identify a record with. For example, I index email address as no duplicates; there can be only one customer that's [email protected]. That's how I look up customers in my database. If you try to type in another person with the same email address, you'll get an error message.
Some other companies I've seen use phone number to uniquely identify their customers (one of which I have membership at), and every time you go buy something, you get enough of a receipt to wallpaper your house with. I'm not going to mention any names, but they ask for your phone number to give you your discount points, and that's probably an indexed field in their database. And if it's not, their IT department is slacking.
All right, so that's "yes, no duplicates." Now 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 on the customer base on their last name. You may want to index that field but allow duplicates. You still want to allow multiple Smiths, for example, or Kirks. If you search on it a lot, if you sort on it a lot, you want to index it and duplicates are OK.
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, one at a time, 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, back in the day—big, giant, thick books that the phone company would drop off at your house? That's better. I haven't seen those in a long time now. I think they're just for businesses now, more of an advertising thing. But remember the phone book? I'm old enough to remember that. Imagine trying to find a person in an unsorted phone book. That's basically what indexing does.
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 on that field. Access can use a faster algorithm (I'm not going to go into the details right now) to quickly find a value in that sorted index.
I have been thinking about doing some classes on computer science, which is the actual math and logic behind this stuff. If you are interested in that, post a comment below. I took that stuff in college back in the 90s. It was pretty fascinating, learning different algorithms for sorting and such.
Now, you might ask, why don't I just index every field? Well, you don't want to index too many fields. Doing so will increase the size of your database needlessly. Every index table adds to the size of your database, and indexing slows down updates and appends if you change or add records, because Access has to rebuild that entire 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 your fields. If you've got a dozen people doing data entry all day long like in a call center, and you only run reports on that information once a month, go easy on the indexing. It's all situational, based on the needs of your business.
If you do a lot of searches and sorts on a particular field and it's running slow, try indexing that field.
I'll talk about field types. You can basically index every type of field you should be using, although you might not always want to. You can index short text, long text, number, date/time, autonumber, yes/no, and hyperlink fields. You cannot index all of the evil field types that you shouldn't be using anyway. OLE object (basically pictures or documents stored in the database)—we don't do that. Calculated fields—we don't put calculated fields in our tables; we save that for queries. And attachments—again, evil.
Want to see all the evil Access stuff? I have a page for it. I'll put a link down below you can click on and go read all this stuff. I'm going to make a video on this eventually. I'm still compiling it.
In older versions of Access, you couldn't index memo fields, which are now long text fields, and in fact, if you're watching an older version of some of my lessons, I mention that you can't index memo fields. You can now, but I don't really see much of a point in doing that. It can really be a hit on the performance of your database if you have indexed long text fields, so use those sparingly.
Here's a weird one: by default, Access will automatically index fields that end in ID, key, code, or num. Those suffixes, if you put a CustomerID in a table—even as a foreign key—it's going to get indexed ("duplicates OK"). But Access will also index the word aluminum if it's a field, which I think is kind of silly.
If you don't like this, you can disable this under File > Access Options; go to Object Designers, and you'll see "AutoIndex on Import/Create." It'll turn these auto indexes on for these field types anytime you create a new field or if you import it from something like Excel. Just remove these options here. I usually leave ID in there, but key, code, and num I usually get rid of. That's up to you. Will the extra indexes hurt you? Probably not much, but that's just something to think about.
There's an Indexes dialog box; you can view all of the indexes in a table by clicking on the Indexes button on the Table Design menu (this little guy right there). It opens up this thing—I know it's a little cut-off picture of my screenshot. In here, you can create something called a multi-field index, which is rare, but I use them once in a while.
Let's say you routinely search or sort based on last name and first name together—Smith, Joe, for example. Creating a multi-field index means that your database now has an index built containing these two fields together, and it doesn't have to search through two separate index tables. This only really makes sense if you've got a zillion records in a huge database, but it can greatly improve performance if that's something you do a lot. I've got a separate video on building a multi-field index and why you'd want to do that; check out that website for more. I'll put a link down below.
Usually, the only time I'll set up a multi-field index is when I'm creating something called a composite key. That's an index requiring two fields in the table to have unique values. For example, here I created an index called "OrderWithProduct" and it indexes OrderID and ProductID, so you can't have the same product listed twice on one order. Those two things have to be unique: order 1/product 2, order 1/product 3, and so on. Again, want to learn more about it? I have a video. I'll put a link down below.
In summary:
- Indexing prevents duplicate values (CustomerID, email address, etc.)
- You can use it for speeding up searches and sorts.
- You can either make it unique values, or duplicates are OK.
But, on the downside, you don't want to overuse them because it will slow down updates when you add or change records and will take up more disk space.
To be completely honest, I usually hold off on indexing until I'm finished building my database. I don't try to think about it as I'm going ahead and working, because this is more of a performance issue—unless you're dealing with the duplicate value thing, like wanting to prevent multiple phone numbers from being the same, for example. But as far as the performance issues go, that's something you can fine-tune later. You can always add or remove indexes when your database is more mature and chugging along, and you're wasting hundreds of megabytes of space.
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. We'll talk about each field, then why you should or should not index it. This class also covers a lot of other field properties—required, allow zero length, that kind of stuff. We talk about compact and repair, backing up your database, and lots more. It's an hour-long class. You can find it on my website. There's a link—I'll put it right down below. Again, it's Beginner Level 4, so it's not an expensive one.
So there you go! There's your fast tip today about indexing. If you have any questions or comments, post them down below. Hope you learned something—I know I did. We'll see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault, where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free Expert class each month after you finish the Beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free Developer class each month after you finish the Expert classes. These are the full-length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond sponsor and have your name or company name listed on a sponsors page. They'll be shown in each video as long as you're a sponsor. You'll get a shout-out in the video, and a link to your website or product in the text below the video and on my website.
But don't worry—these free TechHelp videos are going to keep coming! As long as you keep watching them, I'll keep making more, and they'll always be free.Quiz Q1. What is the primary reason for indexing a field in Microsoft Access?
A. To change the field's data type
B. To speed up searches and sorts on that field
C. To allow more records in a table
D. To prevent table deletion
Q2. Which of the following field types can NOT be indexed in Access?
A. Short Text
B. Number
C. OLE Object
D. Date/Time
Q3. What does setting an index to "Yes, No Duplicates" accomplish?
A. Prevents deletion of indexed records
B. Ensures that all values in that field are unique
C. Makes searching slower
D. Allows only text data
Q4. Why should you avoid indexing every field in a large Access database?
A. It makes reports harder to generate
B. It increases the size of the database and slows down updates
C. It prevents sorting
D. It reduces data entry accuracy
Q5. When might you want to use "Yes, Duplicates OK" when indexing a field?
A. When you want to allow only unique values
B. When you want to prohibit any duplicate values
C. When you allow duplicates but still want fast searching and sorting
D. When you want to hide the field from queries
Q6. What is a multi-field (composite) index used for?
A. To index non-text fields only
B. To ensure unique values based on a combination of two or more fields
C. To convert short text fields to long text
D. To import data from Excel
Q7. Which of the following adds to database size and slows down updates and appends?
A. Having no indexes
B. Indexing too many fields
C. Only using lookup fields
D. Creating relationships between tables
Q8. In what situation might you delay adding indexes until after building your database?
A. When performance and duplicate prevention are not yet an issue
B. When you only use primary keys
C. To increase the default size of your database
D. To allow calculated fields in tables
Q9. What happens if the field name in Access ends with "ID", "key", "code", or "num"?
A. Access automatically creates a lookup for it
B. Access automatically creates an index for it
C. The field becomes hidden
D. The field can only be used in queries
Q10. What significant change occurred with memo fields (now called long text) in recent Access versions?
A. They can now be indexed
B. They can store images
C. They are no longer supported
D. They must always be duplicated
Answers: 1-B; 2-C; 3-B; 4-B; 5-C; 6-B; 7-B; 8-A; 9-B; 10-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on a fundamental topic in Microsoft Access: indexing fields for optimal database performance. Let me share with you what indexing is, why it's important, and how to use it wisely in your Access databases.
First, I want to clarify the terms optimal and optimum. I used to think they were interchangeable, but after looking it up, I found out that optimal is the adjective, as in optimal performance, while optimum is the noun, referring to the best condition or result. It's a fun fact I came across while preparing this lesson, and I always enjoy picking up these little details as I create my videos.
Moving on to the main topic: indexing. Many people who are new to Access tend to overlook or misuse indexing. If your database is small, you might get away with never using indexes at all. However, as your database grows in size, you might notice it becoming sluggish. Proper indexing keeps the performance high as more data accumulates.
The primary use of indexing is to prevent duplicate values in fields where duplicates are not allowed. For example, your primary key should always be indexed to stop two records from having the same identifier. If you use email addresses as a unique way to identify customers, indexing the email field with no duplicates enabled ensures each email can only appear once. If someone tries to use the same email a second time, Access stops it with an error.
Some businesses use other fields for unique identification, like phone numbers. For example, when certain stores ask for your phone number to award loyalty points, it's likely that field is indexed as unique in their system, or at least it should be.
Besides preventing duplicates, indexes can be used simply to speed up searches and sorting. If you often search by last name, you can set the last name field to be indexed, but with duplicates allowed. This way, Access speeds up the lookup and sorting process while still allowing for several customers named Smith or Kirk.
Remember, when you enter data in an Access table, records are stored in no particular order. When you search or sort, Access would otherwise have to scan through all the records individually, which is slow in bigger tables. Think of it like trying to find a specific person in a completely unsorted phone book. If you remember those big books from years ago, you know how tedious that would be! Indexing works by creating a hidden index table sorted by the chosen field, allowing Access to locate information much more quickly using fast search algorithms.
If you're curious about the computer science and math that drives algorithms like these, let me know. I have been considering covering more of those underlying topics in future videos.
Now, you might wonder why not just index every field to maximize speed? The catch is that every index adds size to your database and slows down any updating or inserting of records. This is because Access has to update the relevant index tables every time you change data. If your database involves lots of daily data input and only occasional reporting, use indexes sparingly. Instead, if you frequently search and sort particular fields, especially in large tables, those fields are strong candidates for indexing.
Most common field types in Access can be indexed, such as short text, long text, number, date/time, autonumber, yes/no, and hyperlink fields. However, you cannot index OLE object, calculated, or attachment fields. I recommend avoiding these types in your tables anyway. I often refer to these as the "evil" Access features, and I've put together a page covering them in more detail.
In older versions of Access, memo fields (now called long text) couldn't be indexed, and that used to be advice in some of my older lessons. Today, it is possible to index long text, but I generally avoid it since it can negatively impact performance. Use indexing on these fields only when absolutely necessary.
One quirk to note: Access automatically indexes fields with names ending in ID, key, code, or num when you create or import them. Even words like aluminum might end up indexed if those letters are at the end of a field name, which is pretty peculiar. If you find this behavior unhelpful, you can adjust it by going to Access Options under the Object Designers section and removing those suffixes from the auto-indexing list. Personally, I usually keep ID but remove the others.
Access also offers an Indexes dialog, accessible from the Table Design menu. This dialog lets you see and manage the indexes on your table, and here you can set up what's known as a multi-field index. This is useful if you often search or sort datasets using more than one field together, like searching for customers by both last and first name. Multi-field indexes are most beneficial in very large databases and can significantly boost speed in those cases. Usually, though, I only create multi-field indexes when I need what's called a composite key, where two fields together must hold unique values. For example, if you need to ensure you never list the same product twice on a single order, you would create a composite key on both order ID and product ID.
Let me summarize the key points:
- Use indexes to prevent duplicate values, such as for primary keys or unique identifiers like email addresses.
- Indexing speeds up searches and sorting on the indexed field.
- Indexes can enforce uniqueness, or allow duplicates if needed.
- On the downside, too many indexes slow down updates and increase database size.
I typically wait until my database design is close to finished before adding indexes, focusing more on duplicate prevention during building than performance tweaks. Performance tuning with indexes is easy enough to handle later, once the database is in regular use and you can see where the slowdowns are.
If you want to learn even more about indexing, check out my Access Beginner Level 4 class on my website. In that class, I walk through my database fields and explain which ones I choose to index and why. The course also covers field properties, compacting and repairing your database, backup procedures, and a lot more. You'll find it linked on my site, and it's very reasonably priced.
That covers today's fast tip on indexing in Microsoft Access. If you have questions or comments, feel free to post them. I hope you found this lesson informative. I know I learned a few things preparing it!
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List What indexing is in Microsoft Access
How indexing prevents duplicate values
Using indexing to uniquely identify records
Indexing with "no duplicates" for fields like email
Indexing with "duplicates OK" to speed searches and sorts
How indexing improves search and sort performance
Hidden index tables managed by Access
Drawbacks of indexing too many fields
Impact of indexing on database size and update speed
Situational considerations for applying indexes
Types of fields that can be indexed in Access
Field types that cannot be indexed
Access auto-indexing based on field name suffixes
How to disable Access auto-indexing on import/create
Using the Indexes dialog box in Table Design view
Creating multi-field (composite) indexes
When to use multi-field indexes for performance
Composite keys requiring combined unique values
Recommendations on timing index creation
Risks of overusing indexes in Access |
