we all know how powerful my skill indexes are but do you know how they work in this video i'll show you exactly how mysql indexes [Music] work now before we see how mysql indexes work let's take a look at an example first and then see how it works with respect to this example recently i put out a video showing you how powerful mysql indexes are we took a query that took almost two seconds and brought it down to three milliseconds do check it out i'll leave the link in the description or you could find the video at the top right corner of your screen check it out and you'll see all the ways we can create indexes and really how powerful they are now here i'm using the exact same table which is the authors table and notice we have almost 3 million records let me run a very simple query on this i'm trying to find all the instances where the first name is jonas we got the results and we see that it took almost 103 milliseconds now if i add an index to this column first name the query should get faster now let's go ahead and quickly add an index again check out the video in the description uh you'll see all the ways you can create indexes and how powerful they are and you'll also see how we can create indexes for multiple columns here i'm using just the alter table statement and i'm creating a new index by saying add index the index name which is the author index and just one column which is the first name column let me create this index so you see it's taking some time because when you create an index mysql has to take all the values from the first name column and store it in a separate location in a certain order so that searching is easier and quicker the index is done let's run the same query again you see it was much faster notice that it took 0.0026 seconds that is 2.7 milliseconds so the query went from 103 milliseconds for the same 50 rows to 2.7 milliseconds now that is fast now let's see how mysql index works and how it worked with this example now here i'm showing the authors table without the other columns and just the first name column to make it simple i want us to think of database indexes the same way how the index in a book works when you're searching for a certain word there are two ways you can find it you could either go through each and every page or you can go to the index find the word you're looking for and then go to the relevant pages mysql indexes work the exact same way now when i'm searching for the word jonas now without an index mysql would scan through each and every row till it finds the values jonas when it does it'll mark down the location where it found the word jonas and then it would move to the next row till all the rows are scanned and once it's done it will go back to the locations where the value existed it will pull out all the data and it will present to the user this is why regular searches or regular filters are slow because mysql has to go through each and every row now let's see what happens when we create an index first notice that to the left of the authors table we have all the pointer locations these are just locations these are basically pointers which point to different locations in the authors table to the left we have the index column here what we notice is when the index is created mysql takes all the data from the first name column and stores it in a separate location and that's why it took some time because it had to create separate storage for it and you notice that it's set it up in an ordered list and it's also made a note of all the pointer locations now this is basically how a b3 structure works when we index think of it as a binary search so when we search for jonas what mysql does now on the index column is since it's ordered mysql will know that each and every value in terms of its byte value will be greater than the next one and so when you're searching for the word jonas it will first look at the middle value of this column which is lilly and then it will check if the value of jonas in byte is lesser or greater than lilly if it is greater it will look to the right of the column or in this representation bottom below lily if it was lesser it would look above since the value of jonas is lesser than lilly it'll look above lily the same procedure will continue next it will look for the middle value among the top row which is hana and then again it will check if it's greater or lesser by this method of elimination mysql can find jonas without having to go through each and every row in the column once it finds a match which is the value jonas it will check each and every row below it till it continues to find jonas and when the next word is not jonas it'll realize that it's found all the value switches equal to jonas it'll now take the corresponding pointer locations which in this example is just p10 it'll go back to the original table which is the author's table pull out the data from the pointer location p10 and present it to the users this is how the b3 or this is how the index works in mysql without having to scan through each and every word it will take all the data stored in a separate location in an ordered list and then it will use something similar to binary search which is called as b tree here and then by means of this elimination it will find the value so therefore querying using indexes are much much faster there are different kinds of indexes like the primary key the unique the regular index that we looked at and full text indexes these all use the same b3 concept we will take a look at the different kinds of indexes in a later video but for now i hope with this we have a clear understanding of how basic mysql or database indexes work if you liked the video please hit that like button and subscribe to my channel thank you for watching