Indexing

Quick reference

• 00:05 In this video, I wanna talk about indexing.
• 00:08 And indexing is pretty important.
• 00:10 It's a fundamental concept of pretty much all databases that you're ever gonna
• 00:14 deal with.
• 00:14 And it's something that sorta gets glossed over sometimes.
• 00:17 But in our database here, in our table, we only have a few records.
• 00:21 We've got seven, right?
• 00:22 So indexing is not that important.
• 00:24 But as I've said over and
• 00:25 over throughout this course, in real life you can have thousands,
• 00:28 tens of thousands, millions of records, millions of rows in your database.
• 00:32 And searching through all of those records, all of those rows takes a lot or
• 00:37 can take a lot of horsepower, a lot of computer power, a lot of processing power.
• 00:41 And depending on the number of rows and records you have it can slow things down.
• 00:46 Well, indexing is a way to get around that.
• 00:48 Indexing speeds up the search process.
• 00:50 And you can index a column, any column, all columns really I guess.
• 00:54 But an index is sort of like it sounds, like it's an index, like a book index.
• 00:59 At the back of a book,
• 01:00 if you wanna look up a certain thing, you look it up in the index.
• 01:02 You find exactly where it is.
• 01:04 And then, boom, you can just search specifically for that thing.
• 01:07 And that's what we're gonna do here with our database, create an index.
• 01:10 And the thing about it is is once you create it,
• 01:13 it kinda works in the background.
• 01:15 And you're not gonna have to do a lot with it.
• 01:17 Just know that it speeds up the searching of your database.
• 01:20 So, to create an index, it's pretty simple.
• 01:23 We just go, CREATE INDEX, and then we just give it a name.
• 01:30 Let's just call it, I don't know, my_index and then ON, oops, ON.
• 01:36 And give it your table,
• 01:39 [dbo],[Current_Customers], customers.
• 01:45 There we go.
• 01:46 And then designate which column you want to add the index to.
• 01:51 So let's say Customer ID.
• 01:55 And you can add multiple columns by just stringing these along with commas,
• 02:01 like we've done for other things throughout this course.
• 02:03 So now if we run this, we kind of look at this and
• 02:06 nothing really seems to have changed.
• 02:08 But we have created this index.
• 02:10 And like I said it runs behind the scenes.
• 02:12 It does things behind the scenes.
• 02:13 And now we don't have to worry about it again.
• 02:15 Now to drop that index to get rid of it,
• 02:18 to delete the index we just created you can do that too.
• 02:21 Like I said in a couple of videos ago when we delete things,
• 02:23 we often refer to them as dropping them.
• 02:25 So to drop that, we just go DROP INDEX and then the name.
• 02:31 We called ours my_index.
• 02:33 on, and then list the table name.
• 02:35 So I'm just gonna copy this.
• 02:38 Paste that in there.
• 02:40 Go ahead and delete that.
• 02:42 Now if we run this thing again, boom, our index has been deleted.
• 02:45 Again, we can't really see it because it's happened behind the scenes.
• 02:51 So that's indexing.
• 02:52 In the next video we'll talk about primary keys.

Lesson notes are only available for subscribers.