Microsoft Access 2013 Tutorials
Database 101 / Understanding Indexes pt. 1
Subtitles of the Movie (Microsoft Access 2013 / Database 101 / Understanding Indexes pt. 1)
Indexes are another piece of database functionality that you will definitely hear about pretty quickly once you dive into the database world. And in this video Part 1, I'm going to divide this one into two. So we're going to do a Part 1 and a Part 2 I am going to show you a very simple little exercise that will help you tremendously. As a matter of fact, this will kind of launch you ahead of some fairly experienced database administrators out there. Because a lot of people really don't take the time to understand how indexes are affecting their tables. But you're going to know that in just a minute here. So first of all let's stop and think just a minute here, indexes make data searches faster. As long as we understand how the index is organized. Now think about the phone book. If, if I ask you to find my phone number in the Nashville phone book, I don't even know if it's in there but anyway you're going to grab that phone book. And let's say that it's a white page phone book about two or three inches thick, you're not going to turn to the first page for my name, why? Because you know that phone book is in last name order, it's been indexed. It's last name then first name and it's alphabetized that way. So you're not going to start looking in the As, my last name starts with an L. Right. Mark Long so you're going to go and you're going to flip through until you start to find the Ls and you're going to find the Lanes and the Langleys and the Lamberts and all those people and then eventually you'll see Long. And once you find Long you'll go to the first one that says Long and you'll look for the Marks. Right. And you can skip down through there and you'll either find me or you won't. And then you'll eventually get to the Longleys, L-O-N-G-L-E-Y and at that point you know you can close the phone book because it's in an index order, last name first name and there simply won't be any more Longs after that last one. So that is the same exact thing that's happening inside our tables. Now let me give you an example here. In this example just like I just described I want you to find the last names that begin with L in this list, find all of them for me. Okay. Go. Tick tock, tick tock. Right. Here's what you got to do find them here, they're hidden. This is not an indexed list. Right. And so I have to look. I'm looking for last name beginning with L, Jack Jones no, Frank Harris no, Bob Lane yes there's one. Okay. Mary Ashton no, that's, she's not, that's not an L. George Cole now I'm being a little bit facetious here. We don't actually think this way but we are doing this subconsciously. Right. Janet Langley okay there's an L, we had one up here, so that's the second one. Arthur Hamilton no, Eric Walker no, Fred Lawrence yes there's another one so there's at least one, two three if I haven't missed any and then Todd Yates. Okay. So I'm going to say there are three names in this list that begins with L. Noticed a couple of things that happened. I had to look at every name in this list. In database technology terminology that's called a table scan, I had to scan every record and that is a four letter word, S-C-A-N in databases. Okay. You do not want to do table scans in databases. I have to look at every row that's not efficient. So now let's compare that. Okay. Now let's index that same list alphabetically and try it again and notice what your brain is doing here. Okay, let's look for the Ls, how many do we have? Well Ashton, Cole, Hamilton, okay here's where the L start. Lane, Langley, Lawrence, Walker that's all of them, these three right here. I can immediately see that there are three. Now you could have done it much faster than that but since we know how the index is set up, we understand the structure of the index. We can look for the first L and then keep looking and as soon as we see one that's not an L we know that we're finished with the Ls and it's very easy to find these three. Your database, your database engine is doing the exact same thing. So as I end Part 1 here let me ask you a question? This is so cool, why don't we just index every single column in every one of tables? That way we could find anything super fast, super efficient and everybody goes home, maybe even early. You know how that, never mind, I won't even go there. But maybe everybody goes home early and everybody's happy because we can find our data so quickly. There's a problem with that, welcome to life once again, life invades the database along with everything else. It won't necessarily work that way and in Part 2 I'm going to show you why indexes, while they're great for finding data, they can be problematic in other areas. So join me in Part 2. How's that for a teaser? And we'll take a look at kind of the dark side of indexes. Okay.
Tutorial Information
| Course: | Microsoft Access 2013 |
| Author: | Mark Long |
| SKU: | 34405 |
| ISBN: | 978-1-61866-090-9 |
| Release Date: | 2013-02-01 |
| Duration: | 7 hrs / 89 lessons |
| Work Files: |
Yes |
| Captions: | No |
| Compatibility: |
Vista/XP/2000, OS X, Linux QuickTime 7, Flash 8 |
VTC Sign up & Benefits
- Unlimited Access
- 81,350 Video Tutorials (14,200 free)
- Video Available as Flash or QuickTime
- Over 715 Courses
- $30 for One Month Access
- Multi-User Discounts Available
