Learning MySQL - Using Indexes

okay our next MySQL topic is indexes so what is an index if you imagine the database tables that you're working with having hundreds of thousands or millions of records inside of them it can take a fair bit of processing power to be able to find things when you apply a filter when you're doing a join between tables so in order to help out the database we're gonna create an index which really tells the database this is a really important column I want you to remember sort of where different ranges of things are inside that column where can you find the number 72 inside that column where can you find the number 1008 inside that column so what's it's highlighting what's really important so it can create this sort of tree structure which will help it be more efficient to search through that table when you run your queries now don't worry about the tree structure don't worry about how it's working internally what we're gonna do here is just look at how to create an index and how to add that into our code okay so this is the reference right here I'm going to provide this link inside the description for you on how to create an index there's actually a few different ways that we can do it you can create an index when you're creating the table that's actually sort of the preferred way to do it but if your table already exists you can add other indexes as long as it's not a primary key you can use the alter table syntax to add a key or you can just run a create index command all on its own so I'm gonna work with the database that I've got created but I'm not going to work with any of these tables I'm gonna create a brand new table we're gonna look at all three of these methods so inside my database I'm gonna create a new table I'm going to call it products so I'll say create table products and then we have our parentheses and at the end of that we'll say the engine is going to be in ODB ok so there's the basic create table command now inside of here what we want to do is create the columns now there's nothing new about this so I'm just gonna fast-forward through this I'll skip over it and stop when I've got the columns created okay so I finished typing that out we're creating the products table and we're going to have these six columns product ID name category price SKU and a short description field so product ID this is going to be our primary key we're saying that it's an unsigned integer it's gonna automatically defining it as the primary key now this is a type of index we're saying that this is going to be the unique value for every single row by making it an integer in Auto increment my esquel is going to take care of it for us it's gonna automatically put in that number whenever we insert a new product so the name in the category these are just text fields the price is a decimal field up to ten digits with two after the decimal place the SKU is going to be a fixed length char with ten characters in it so it's a unique identifier for each product but it's not a primary key I wanna we're gonna add an index to this and we're gonna say that each of these has to be unique but it's not the primary key for the database so we do a short description varchar' 500 so it's a big text field if we want to do that we're gonna we've put this in here so that we can add a different type of index a full text index okay so there's my basic description now I'm gonna add the first key other than primary key in here because we can do that while we're creating it this is the first method inside of here I'm gonna say index and then give it a name so let's say it's names or ID X names index names then we add a set of parentheses just like we did on the line above here and inside here we can put the one or more fields that we want to use so I could say product name now when we create the table it's going to add an index on this one product name column so it's going to keep track of everything inside of there if you want when you create an index like this you can say you want it do two columns or three if you want just like that so it's gonna combine these two fields to create one index and you would do this if you were running lots of queries or if you were predicting that you were gonna run a lot of queries where you're filtering on both the product name and the category at the same time if you're gonna do that in your select statements then this is a good index to create because okay maybe they're gonna ask about the product name maybe it's gonna be the name and the category but very rarely will you do just the category if that's the circumstances then this is a great index for you to create okay so let's do this let's run that okay so that worked just fine now let's take a look at the table there's products and we'll take a look at the structure inside of there so we've got our columns those were created just fine nothing new about that here's our primary key being defined in the section called indexes and here's the one that we created the additional one idx names and we can see both columns are included as part of this and unique is set to no so we didn't need to necessarily make an insistence that both the name and the category together those two things have to be unique on every row it just it wouldn't make sense to disallow somebody from ever having this two products with the same name in the same category the SKU however these are supposed to be unique so we can add an index on this one and say yeah you know what this has got to be a unique index so let's take a look at doing that one now so we'll jump in here and we can use the second way the first way was with a create table once you've done this then we can do an alter table products and we're gonna say we're going to add index before it was just using the keyword index so now here add index we're gonna give it a name so we're gonna do it on the SKU so let's call it idx SKU now we don't have to put ID X in the underscore I'm just doing that so I recognize the name I'm gonna follow this naming convention for all my indices so index Q and then it's going to be on the column product SKU there we go now if you ever do need to get rid of an index that you've created we can use this command right here so drop index and then give it the name so idx skew on and we provide the table name so this would get rid of the index after we create it okay so add index this is going to create a normal index on just this one single SKU so I'll run that fair enough it added that index if we go back to the structure and we take a look at this let's I'm just going to copy that so we can use it again so here we have it now there's another index cue right here but we didn't defined it as unique so I want to change this I want to make this a unique index just to say that every single row in there is going to be unique okay I'm gonna run just the drop command by itself to get rid of that index now it's gone now to show you the alternate one we're going to add index same as before idx SKU all we're going to change here is this word index and we're to change that to say unique so now we're creating an index which is going to tell the database it needs to track all the things going into here but we're also putting a restriction on it to say that every single value in here is supposed to be unique so let's run that again there we go so did the same thing it created an index and if we go back to the structure down here there's the index name or sorry index Q on the column product SKU and unique is set to yes all right two others that we can do one is a full text and this is the third type of syntax so we can do it in the create table we can do it in the alter table or if you want you can just use the command create index all on its own so we can create an index let's just call it idx text on products that's our table we have to specify that when we're not doing a create table or an alter table we do have to specify which one that we're creating it on so on products and then inside the brackets we're going to use the column short description just like that now this would create a regular one if I change that to unique it's gonna create a unique one but I don't want to try and put a unique restriction on a description field so I'm gonna make this a full text it's a different type of index for full text we should keep the word index in front of that or after that rather so we're creating a full text index on this text field on the table products on this column so all those things together will create this other index for us there we go created that one go back to structure take a look and sure enough there it is now it's they type full text you have to be very careful with the full text once you are dealing with large amounts of text and if you've got a really big text field with a lot of content on there and you're creating a and index on that you're putting a lot of extra work on the database to do this you have to consider whether or not you're going to be doing many searches on this if you're going to be doing a lot of searches on this field then it might make sense to do an index on this but you wouldn't ever put indexes on all the fields like I'm doing here this is really just an example to show you the different kinds of indexes that you can create think about what the queries are that you're going to be writing what data is it that you're going to be searching for inside your table what filters are you going to be applying to your table so one last one that I want to show you so creating an index where we want to search on part of a field the product SKU is a great example of where we could do this so I'm going to create an index called ID X SKU 3 on the table products and then on the column product SKU but we add an extra set of parentheses here and then I can put in the number of characters that I want to use so what I'm doing is I'm actually creating an index on just the first three characters of this char field so I know okay maybe the first three characters of the product SKU indicates what category a belongs to or what type of product it is and maybe this is the kind of look up that I'm going to be doing all the times want to create this special kind of index where the first three characters are what I'm going to be looking at all the time this is going to be what it uses when it does the sorting and searching so I run that and there we go so it's that last little set of parentheses that we can put on the column name which will define how many of the characters starting at the very beginning that we want to add this index to all right so that gives us the ability with create table alter table or just on its own to do create index we can do drop index as well to get rid of them and there are those four different kind of indexes we've got primary unique just a regular index and a full text index so you've got those four kinds that you can play with to optimize and that is really the purpose of an index it's to optimize when you're running your queries don't just arbitrarily go through and add indexes like this I mean on a table that's only got six columns I shouldn't really have five indexes I mean sure I'm going to make a search extremely efficient regardless of what search it is that I'm doing but I've added all this extra overhead so every time there's more columns being at or more rows being added every time there's new data going into that table I'm forcing my SQL to go through and redo its indexes it has to go through and recreate all these indexes to make sure that it can do these searches and all the extra data that's being stored in these indexes as well it's creating this extra information off on the side to be able to do the searches faster so you have to weigh that when you're working with indexes so hope that makes sense I will leave the link to the create index page inside the reference I'll leave another link for the entire MySQL playlist and as always thanks for watching you

Channel: Steve Griffith - Prof3ssorSt3v3

Views: 38,195

Rating: 4.952024 out of 5

Keywords: MAD9022, web development, mad9023, mad9124, mad9135, mad9145, learn mysql, learning mysql, mysql tutorial, intro to mysql, mysql for beginners, learn sql, learning sql, intro to sql, sql for beginners, sql tutorial, mysql indexes, create index, drop index, sql indexes, mysql tutorial for beginners

Id: yWj320oEQEo

Length: 15min 0sec (900 seconds)

Published: Sat Jun 01 2019