WEBVTT 00:00:00.160 --> 00:00:01.590 Hi guys and welcome back. 00:00:01.590 --> 00:00:05.400 In this video we're going to learn what a SQL index is. 00:00:05.400 --> 00:00:07.260 This is going to be a slightly more theoretical 00:00:07.260 --> 00:00:08.770 lecture than the ones before, so please 00:00:08.770 --> 00:00:11.480 do bear with me, but it's important to understand 00:00:11.480 --> 00:00:14.830 SQL indices, at least on the surface. 00:00:14.830 --> 00:00:17.300 Or is it indexes? I don't even know. 00:00:17.300 --> 00:00:20.710 So, here's the gist of it. An index is associated 00:00:20.710 --> 00:00:23.600 with a specific column in a table, 00:00:23.600 --> 00:00:27.050 and it helps speed up filtering and sorting operations, 00:00:27.050 --> 00:00:30.670 really anything that uses that column specifically. 00:00:30.670 --> 00:00:33.900 But it slows down writing to the table. 00:00:33.900 --> 00:00:36.390 So, clearly, the index is going to help you out, 00:00:36.390 --> 00:00:39.040 it's going to be useful if you are reading 00:00:39.040 --> 00:00:41.990 a lot of data from a table, filtering and sorting, 00:00:41.990 --> 00:00:43.890 and you're no writing that much. 00:00:43.890 --> 00:00:47.810 Here's how they work. Let's say we've got this table here. 00:00:47.810 --> 00:00:50.970 This is a pretty long table. We've got 12 people in it. 00:00:50.970 --> 00:00:54.000 They have their names, years experience, and salary. 00:00:54.000 --> 00:00:57.620 Let's say that the ID column is the primary key, 00:00:57.620 --> 00:00:59.380 and most database systems are going to create 00:00:59.380 --> 00:01:02.030 an index for primary keys for you. 00:01:02.030 --> 00:01:05.950 So here is our index on the right. 00:01:05.950 --> 00:01:09.340 Notice that we've picked somewhere amongst the middle 00:01:09.340 --> 00:01:13.203 of our table to be the top of our index tree. 00:01:14.650 --> 00:01:18.010 And then the database system is going to build out 00:01:18.010 --> 00:01:22.750 this tree by putting less than eight on the left of it 00:01:22.750 --> 00:01:25.100 and values greater than eight on the right. 00:01:25.100 --> 00:01:27.680 So three and five go to the left of eight, 00:01:27.680 --> 00:01:29.410 11 has gone to the right, 00:01:29.410 --> 00:01:31.410 and then, similarly, for those nodes 00:01:31.410 --> 00:01:33.680 like, for example, three and five, we've got 00:01:33.680 --> 00:01:35.560 values less than three on the left, 00:01:35.560 --> 00:01:37.790 values between three and five in the middle, 00:01:37.790 --> 00:01:40.000 values greater than five on the right. 00:01:40.000 --> 00:01:41.420 And so the database system is going to do 00:01:41.420 --> 00:01:44.540 all of that for us. It's going to construct this tree 00:01:44.540 --> 00:01:47.820 parting from the index. 00:01:47.820 --> 00:01:49.860 And here we can assume that the ID column 00:01:49.860 --> 00:01:52.240 is a primary key. Most database systems are going to 00:01:52.240 --> 00:01:55.560 make indices out of primary keys for you. 00:01:55.560 --> 00:01:57.930 So here's how they work. 00:01:57.930 --> 00:02:01.830 Imagine we're looking for an employee with ID 9. 00:02:01.830 --> 00:02:04.610 Normally, without an index, we just start at the top 00:02:04.610 --> 00:02:06.890 and go through the rows one by one, searching. 00:02:06.890 --> 00:02:09.060 So, we would have something like this, 00:02:09.060 --> 00:02:10.820 we would start at the first row, 00:02:10.820 --> 00:02:12.820 then we would move over to the second row, 00:02:12.820 --> 00:02:15.057 it's not that one, so we move over to the third row, 00:02:15.057 --> 00:02:17.120 and it's not that one, and so you get the gist of it. 00:02:17.120 --> 00:02:19.550 It goes on and on until you get to the 9th row 00:02:19.550 --> 00:02:22.060 and that's the one that matches. 00:02:22.060 --> 00:02:25.226 Clearly, not so fast. You have to go through 00:02:25.226 --> 00:02:27.950 at least nine numbers, in this case, 00:02:27.950 --> 00:02:30.530 to find the one you want. 00:02:30.530 --> 00:02:33.610 With an index, it's going to be a little bit different. 00:02:33.610 --> 00:02:36.260 You need fewer jumps. You need exactly 00:02:36.260 --> 00:02:38.180 log of N jumps, in fact. 00:02:38.180 --> 00:02:40.510 The mathematics for that are not going to be explained 00:02:40.510 --> 00:02:42.700 in this video, but you need three jumps 00:02:42.700 --> 00:02:46.003 because you start at eight, then you go to 11, 00:02:46.003 --> 00:02:49.040 and then you go to nine, and you find it there. 00:02:49.040 --> 00:02:50.950 Something important about these indices, 00:02:50.950 --> 00:02:54.840 well, it's a feature of B-trees, which is what an index is, 00:02:54.840 --> 00:02:58.670 is that no branch must be much longer than the others. 00:02:58.670 --> 00:03:01.330 So that's an interesting problem 00:03:01.330 --> 00:03:03.660 when you start to add new numbers. 00:03:03.660 --> 00:03:05.816 Clearly, for example, if we wanted to add the number, 00:03:05.816 --> 00:03:09.420 let's say, 6.5 for the purposes of example, 00:03:09.420 --> 00:03:12.270 and then you would add it somewhere around here, 00:03:12.270 --> 00:03:15.170 but then that makes this branch longer than the others, 00:03:15.170 --> 00:03:17.090 so when you start adding new numbers, 00:03:17.090 --> 00:03:19.760 sometimes the binary tree has to move things around 00:03:19.760 --> 00:03:20.950 to make sure that the branches 00:03:20.950 --> 00:03:23.940 are not too different in length. 00:03:23.940 --> 00:03:28.030 So how do we code a B-tree? That's what this is. 00:03:28.030 --> 00:03:29.800 We don't have to. You don't need to, 00:03:29.800 --> 00:03:32.080 we're not going to teach you how to code a B-tree. 00:03:32.080 --> 00:03:34.730 The database does it all for you when you create an index, 00:03:34.730 --> 00:03:37.890 it's just interesting to know what it's structured like 00:03:37.890 --> 00:03:39.600 so that you know why it's faster. 00:03:41.000 --> 00:03:42.660 But it's important to understand that 00:03:42.660 --> 00:03:45.400 an index does affect performance. 00:03:45.400 --> 00:03:48.280 Faster reads when filtering or sorting, 00:03:48.280 --> 00:03:50.170 basically anything that uses the column 00:03:50.170 --> 00:03:52.450 specifically for an operation is going to be faster 00:03:52.450 --> 00:03:53.960 because the database is going to be able 00:03:53.960 --> 00:03:57.040 to use that tree to find things, 00:03:57.040 --> 00:03:59.500 rather than going through the table itself, 00:03:59.500 --> 00:04:00.840 but it's going to be slower 00:04:00.840 --> 00:04:02.780 because every time we want to insert a row, 00:04:02.780 --> 00:04:04.910 the index need to be kept updated 00:04:04.910 --> 00:04:07.070 and branches potentially moved around 00:04:07.070 --> 00:04:08.340 so that they are the same length, 00:04:08.340 --> 00:04:10.070 or close to the same length. 00:04:10.070 --> 00:04:13.250 Note that an index is a table, so you also need 00:04:13.250 --> 00:04:17.760 more disc space to store the index if you create one. 00:04:17.760 --> 00:04:19.710 All right, let's go and add an index 00:04:19.710 --> 00:04:21.540 to our app's database. 00:04:21.540 --> 00:04:24.710 Note that normally you'll only add indices 00:04:24.710 --> 00:04:27.640 when queries are taking very long. 00:04:27.640 --> 00:04:29.330 If your queries are taking long, that's a sign 00:04:29.330 --> 00:04:31.210 that you can use an index to speed things up, 00:04:31.210 --> 00:04:34.440 if the slowdown is happening in a WHERE clause 00:04:34.440 --> 00:04:36.830 or an ORDER BY, or something like that. 00:04:36.830 --> 00:04:40.010 Don't add indexes everywhere, or indices everywhere, 00:04:40.010 --> 00:04:42.600 because that's going to be a waste of processing power 00:04:42.600 --> 00:04:44.280 and disc space. 00:04:44.280 --> 00:04:45.880 All right, let's go and do that.