WEBVTT 1 00:00:00.240 --> 00:00:01.730 Hi guys and welcome back. 2 00:00:01.730 --> 00:00:03.180 In this video we're going to learn 3 00:00:03.180 --> 00:00:06.070 how to use SQL to create an index. 4 00:00:06.070 --> 00:00:08.350 We've decided not to go with a presentation video 5 00:00:08.350 --> 00:00:10.700 in this case because the create index 6 00:00:10.700 --> 00:00:13.160 is so similar to create table that really 7 00:00:13.160 --> 00:00:14.390 there's not much difference, 8 00:00:14.390 --> 00:00:17.140 so we thought we'd spare you the presentation 9 00:00:17.140 --> 00:00:19.640 and instead we would jump directly into db browser 10 00:00:19.640 --> 00:00:21.910 to show you how to create an index. 11 00:00:21.910 --> 00:00:23.013 So let's get started. 12 00:00:24.170 --> 00:00:26.823 Something important to remember, though, 13 00:00:26.823 --> 00:00:28.800 that we've measured before is that you should not 14 00:00:28.800 --> 00:00:31.540 jump into adding indices to a database. 15 00:00:31.540 --> 00:00:33.800 You should first think how often you're gonna read 16 00:00:33.800 --> 00:00:36.530 and write from the table and that includes 17 00:00:36.530 --> 00:00:37.940 searching and filtering for data. 18 00:00:37.940 --> 00:00:40.940 If you're gonna read and filter and search a lot more 19 00:00:40.940 --> 00:00:44.650 than writing, then it's maybe worth having. 20 00:00:44.650 --> 00:00:45.950 For the sake of learning though, 21 00:00:45.950 --> 00:00:49.601 we're going to add an index to our release timestamp 22 00:00:49.601 --> 00:00:52.440 column here in our movies. 23 00:00:52.440 --> 00:00:54.080 So the release timestamp column, 24 00:00:54.080 --> 00:00:55.800 let's say that we're doing a lot of searching 25 00:00:55.800 --> 00:00:56.633 with release timestamp. 26 00:00:56.633 --> 00:00:59.030 We're always looking at which movies we're waiting for 27 00:00:59.030 --> 00:01:00.850 and so forth so we want an index in here 28 00:01:00.850 --> 00:01:02.490 to speed up the queries. 29 00:01:02.490 --> 00:01:05.590 Totally acceptable explanation but obviously in this case, 30 00:01:05.590 --> 00:01:08.230 we wouldn't be adding a query for this application 31 00:01:08.230 --> 00:01:09.350 because clearly it's very small, 32 00:01:09.350 --> 00:01:11.930 we're only using it here but as the application grows, 33 00:01:11.930 --> 00:01:13.550 maybe you'll wanna do things differently. 34 00:01:13.550 --> 00:01:14.670 Totally acceptable. 35 00:01:14.670 --> 00:01:17.650 Just remember to add the index when you need it 36 00:01:17.650 --> 00:01:19.610 and not really before. 37 00:01:19.610 --> 00:01:22.120 But, nonetheless, to execute SQL here, 38 00:01:22.120 --> 00:01:25.370 we're gonna add an index that is create index, 39 00:01:25.370 --> 00:01:26.430 exactly like create table 40 00:01:26.430 --> 00:01:28.230 but we're creating an index instead. 41 00:01:28.230 --> 00:01:31.120 Remember that we mentioned that an index is a table. 42 00:01:31.120 --> 00:01:34.660 All the data for how the index is ordered 43 00:01:34.660 --> 00:01:36.830 is stored in a table. 44 00:01:36.830 --> 00:01:40.340 So just like create table, we have to provide a name 45 00:01:40.340 --> 00:01:43.870 for the index, for example, idx movies release. 46 00:01:43.870 --> 00:01:47.600 Normally you prefix index names with idx on this course, 47 00:01:47.600 --> 00:01:50.460 not necessary though, but it's the convention. 48 00:01:50.460 --> 00:01:53.860 Then, differently to create table, we have to say 49 00:01:53.860 --> 00:01:58.210 on which column and table we're going to apply the index. 50 00:01:58.210 --> 00:02:01.020 So we are applying it on the movies table. 51 00:02:01.020 --> 00:02:02.760 And then in brackets, the column. 52 00:02:02.760 --> 00:02:05.360 So we do release timestamp. 53 00:02:05.360 --> 00:02:06.240 So that's it. 54 00:02:06.240 --> 00:02:07.380 We create index. 55 00:02:07.380 --> 00:02:08.213 We give it a name. 56 00:02:08.213 --> 00:02:11.710 And we say on which table and column we're gonna apply it. 57 00:02:11.710 --> 00:02:13.210 You can also do a couple other things. 58 00:02:13.210 --> 00:02:16.860 You can create a unique index and that's gonna ensure 59 00:02:16.860 --> 00:02:19.780 that any values for movies release timestamp 60 00:02:19.780 --> 00:02:21.100 have to be unique. 61 00:02:21.100 --> 00:02:23.200 That's the assumption when you do this. 62 00:02:23.200 --> 00:02:26.750 Also remember that when you create a primary key, 63 00:02:26.750 --> 00:02:29.550 that normally creates an index for you as well. 64 00:02:29.550 --> 00:02:32.900 And so the ID column of the movies table for example, 65 00:02:32.900 --> 00:02:34.970 already has an index associated with it. 66 00:02:34.970 --> 00:02:37.340 You don't have to create another index. 67 00:02:37.340 --> 00:02:38.173 Okay. 68 00:02:38.173 --> 00:02:39.006 So this is all we need. 69 00:02:39.006 --> 00:02:40.492 Now you can run it here if you want 70 00:02:40.492 --> 00:02:43.180 but I'm gonna cut it and we're gonna run this 71 00:02:43.180 --> 00:02:45.130 directly in our application. 72 00:02:45.130 --> 00:02:47.640 So we're gonna go over to database.py. 73 00:02:47.640 --> 00:02:49.770 We're gonna add our query here. 74 00:02:49.770 --> 00:02:51.480 And the reason why you might wanna add it here 75 00:02:51.480 --> 00:02:53.330 is so that you can create the index 76 00:02:53.330 --> 00:02:55.330 when you create the tables so that you don't miss it 77 00:02:55.330 --> 00:02:58.270 in case that the database gets deleted. 78 00:02:58.270 --> 00:03:00.610 So we will add a new query here. 79 00:03:00.610 --> 00:03:05.610 We're gonna create release index, and that's gonna be this. 80 00:03:06.600 --> 00:03:08.980 Do note that because this is running every time 81 00:03:08.980 --> 00:03:10.180 the application starts, 82 00:03:10.180 --> 00:03:13.900 you wanna do create index if not exists. 83 00:03:13.900 --> 00:03:17.290 Just like creating a table, you add the if not exists there. 84 00:03:17.290 --> 00:03:19.720 That's only going to create it if it's not there already. 85 00:03:19.720 --> 00:03:20.553 And that's it. 86 00:03:20.553 --> 00:03:24.280 Then you copy that and we copy this and we put it in there. 87 00:03:24.280 --> 00:03:26.140 Now we're creating the index 88 00:03:26.140 --> 00:03:27.770 every time we create our tables. 89 00:03:27.770 --> 00:03:30.770 Of course, make sure that this happens after creating 90 00:03:30.770 --> 00:03:33.690 the movies table otherwise the index would try to be created 91 00:03:33.690 --> 00:03:35.850 on something that doesn't exist yet. 92 00:03:35.850 --> 00:03:37.140 So we're gonna save this. 93 00:03:37.140 --> 00:03:40.040 We are going to close db browser. 94 00:03:40.040 --> 00:03:43.000 We're gonna delete data.db. 95 00:03:43.000 --> 00:03:45.740 And then we're gonna run the application. 96 00:03:45.740 --> 00:03:47.290 As you can see, it runs. 97 00:03:47.290 --> 00:03:50.240 Therefore, we didn't get an error when running the query. 98 00:03:50.240 --> 00:03:51.470 Everything worked. 99 00:03:51.470 --> 00:03:54.480 And now, you know, searching for the upcoming movies 100 00:03:54.480 --> 00:03:58.430 is gonna be slightly faster, unnoticeable, though 101 00:03:58.430 --> 00:04:00.250 for this size of application. 102 00:04:00.250 --> 00:04:02.010 But if you were doing something really complicated, 103 00:04:02.010 --> 00:04:04.430 something really large, a lot of rows, 104 00:04:04.430 --> 00:04:08.200 then the index would substantially help speed things up. 105 00:04:08.200 --> 00:04:09.240 But that's about it for now. 106 00:04:09.240 --> 00:04:11.130 There's a lot more to learn about indexes 107 00:04:11.130 --> 00:04:13.950 such as how to remove them, how to use multi-column indexes 108 00:04:13.950 --> 00:04:16.340 and so forth, but you've got the gist indices, 109 00:04:16.340 --> 00:04:18.650 how they work and we're not going to be cumbersome 110 00:04:18.650 --> 00:04:21.520 of those other edge cases in this course. 111 00:04:21.520 --> 00:04:22.980 Thank you guys for joining me. 112 00:04:22.980 --> 00:04:24.200 Thanks for watching this video. 113 00:04:24.200 --> 00:04:25.833 I'll see you in the next one.