- I'm your host, Sean MacKenzie. Thank you for joining me once again on my channel on data engineering. In this episode, we go back to our Microsoft Access playlist. We're going to take a look at indexes and how to use indexes to improve the speed of our queries and lookups and things like that. 

Now, there are quite a few gotchas when you're using indexes. And we're going to explore a bit of that today. But we're going to take a look at how to apply indexes on not just one field, but multiple fields, so that your lookups are super fast. Without further ado, let's get to it. 

Looking for resources for your project? Make sure to check out the additional links in the description. 

OK, so for this episode, what I wanted to do is take a look at indexes. And I've downloaded from the British Library these tables that have detailed records of recordings in them and names of artists and things like that. And as you can see, this one particular table has 1.3 million records in it. 

I can scroll down through these and you can see that there's a ton of records in there. And a million records is not a huge table, but it's big enough that you'd certainly want to start to index your fields if you were experiencing slowdowns in your application. 

And so you can see this BL record ID here, that one actually was indexed on the import. So Access made some decisions for me when I did the import. And then there's all these other fields here. There's a title. There's more information. There's composer information in this table. 

And if I scroll over to the right here, you can see there's some year fields. And then it sort of ends with there is an ISBN column, which we'll maybe take a look at, and there's some other information as well. And so I'll close that. I'm not going to save the changes to layout. And then we can get started. 

Now, I'm just going to go to the Create ribbon here. And I'm going to choose Query Design. And I will just close that pop up that comes up. And I'm going to drag this table that I would like to see onto my Query Design. And I'll grab another table that I can use in an example of what you might see in a real world situation, which is you might join some tables. 

Now, we could join using the BL Record ID there. But in many cases, you have queries where the joins are not primary keys. You're joining based on some additional employee ID or something like that isn't in the actual key. And so you might be joining based off of those. And you might notice that you have very slow query performance. 

Now, in this case, I don't think this will be that slow, but it sort of gives you an idea of what you might experience for that. And so as you can see, I grabbed some fields from the first table, and I'll grab maybe one or two fields from the second table. 

But then, what we're going to do is we're actually going to take and we're going to use that ISBN there, and we're going to join those using the designer here. And so I'll join based off of ISBN. And we'll also join off of the publication date. And so that's something you might see in a real example. 

And so now we've got two different joins on there. And we're using that names table and the detailed records table. And we'll be getting records from that. And so I'll save this. And I'm just going to call it matching ISBN or something like that. And we'll go OK. 

And what we see is that if we take this one and we run it, then this query looks like it takes a minute, but it's OK. But you might have a query that is just super slow. And that's where an index might help you. Now, indexing can be kind of an art form, and you'll get used how to implement them. 

And often, if you know that there's one particular field that gets used a lot in either lookups on your forms, like your data entry forms, and you have to look up based on some code that is not a primary key, or if you are using them in joins or things like that-- I've just found AC/DC in here, so that's pretty cool. 

But basically, when you notice that there's something in your program that's very slow, that's when you might consider adding an index to a field, if you notice that the slowdown is related to a particular field. And often that's the case. There are some gotchas, though. You have to be very careful not to overindex or put too many indexes on. But we can come back to that. 

So what I did there is I went into the design of the table, and I just clicked on the Indexes button at the top. And as you can see, this table was auto created during the import process. And it did have some indexes on it. It had one primary key index on ID, and the other two, Access just thought those would be good fields to have an index on. And maybe they are. 

But for our query that we were looking at, or for our situation, we wanted to add an index. And what I'm showing you here is I'm showing you how to do a two-field index. So that uses two fields that get indexed together to make it super fast. 

And so we're going to use the ISBN and the publication date together. And so you can see also that in the bottom left of the pop up there, you can choose whether or not it's a unique index or if it's non-unique or if it allows duplicates and things like that. 

And we can go and take a look at each individual field here in our table, as I scroll down. Sorry the window is so small. But you can see that on each of the fields, it says whether or not it's indexed or not. But when we go to the field that we did index, you can see that it said that-- it said, no, it was not indexed. 

And so you have to be careful. Sometimes there are indexes that are on multiple fields. And the individual field entry might not show that. Now, I'm going to go ahead and go to the second table that we're going to use, the names table. So we're using the detailed records table and we're using the names table. 

And I'm going to create the same an index on the same fields in that table as well. You could imagine if this was your production system, that you would use a couple of fields or maybe three fields for whatever. And I've created an index on that one as well. 

So now we've got two fields in the ISBN year index. And I'll save that. And it builds the index there. And so that theoretically is going to make an improvement on your things like queries and lookups and things like that. And that's really where the index indexes shine. 

Now, like I said before, you do have to be careful that you don't put too many indexes on your tables, because they have a certain amount of overhead. So there's kind of like an art form to it. And you can get better at it with practice. And that's really what you should do. 

So in this case, we have the two joins. And they seem to perform fairly well with the index. And that's one way that you can add indexes. Now, if you're indexing only one field-- for example I've added a couple of tables here. I'm going to use this titles table I suppose. 

And you can see this titles table has the British Library Record ID and a bunch of other stuff on it as well. And ending with the British, I think it's the shelf address. And the data is kind of like the other table. It has 1.29 million records in this table. So it's not a tiny table. It's not a huge table. 

But it's definitely getting into the size where you might want to index something. If you do a particular lookup frequently, that uses a field. And so we might use something like-- in this case, we could use the publication date or year because that is something that might be looked up very frequently. 

The other ID columns are already indexed in this table. And so we'll add an index to the publication date field. And to do that, we can just go into the design of our table. And we can just click on the field that is needing an index. And then we can go to these tabs here on the bottom here. 

And you can see there's a general tab. And this tab has an indexed field on it, or attribute. And we can choose whether or not we want this field to be duplicates OK or no duplicates. In this case, we're going to say duplicates OK, because there are, in fact, many recordings in a particular year. 

And then I'll close this table. And I'll say Yes to save it. And you can see it built the index there, which was-- that was the little progress bar that you saw at the bottom. So now what we can do is we can test it out. We can go to our Create Ribbon, and I'll say Query Design, and I'll close the pop up there. 

And then I'm just going to drag it onto our table, or onto our query here. I'll drag a table on. And I'll grab all of the fields. And I do that by double clicking on the star. So that means my query is going to return all of the fields. 

And then I'll grab that publication date, and I'll put that in there, and I'll click it off the show, because it's already included in the star of all of them. If I put something like 1944 in there. 

Look how fast that query is. It just bam. And that's out of over one million records. And so it's really, really handy. Definitely, it will be faster, especially if you're using that field in a join somewhere. It will definitely make it faster, if you're using it for reports and things like that, which is really, really great. 

And these have been very simple examples. But you can imagine if you've got some pretty massive queries somewhere that have some unindexed fields in them, unindexed fields, then you might see some huge improvements if you do a little bit of indexing. And like I said, it's an art form and do not overdo it. 

Don't put too many indexes on your tables. Use them sparingly if you can. And they can make a huge difference for you. And I can definitely tell you, in my own experience, I have seen situations where there are reports or forms that are excruciatingly slow, like we're talking minutes or over 10 minutes to run for a report or something, that have been solved where you just included a new index and it was super fast. Oh, wow, look at that. And I knew time travel existed. 

Looking to see more topics in this area? Make sure to check out my Patreon. The link is in the description. 

[MUSIC PLAYING]