9 hours 41 minutes
I welcome the module. Five. Intermediate database development to this is less than 5.1 indexes. And this lesson. We're going to briefly discuss what indexes are, how they benefit us, and we will also implement some in our database migration files as well.
So let's begin stepping through these bullet points
so the first bullet point improves operation speed uninvolved database tables,
small data tables can hide or mass performance problems. For example, a table with 100 records might seem quick, but might be become terribly slow when you reach 10,000 records.
Now this is, ah, comment in individuals who are brand new to database development. Sometimes they will leave the index out completely, and it doesn't become a problem until there are a lot of records to go through.
So it is important to get those indexes on columns that make sense to really help improve that database performance,
you decide which columns should be in index primary keys, foreign keys and unique constraints and other types of constraints automatically become indexes. So the my sequel database engine will automatically index your constraints.
You're select columns that front end users would likely search data for
So what that means is that the columns that you expect users to search for data should probably be indexed. For example, if you have a gigantic user table or customer table, it might be a good idea to index the name of the customer
because typically your front and users would be searching for customers by name
or by phone number, so phone number might be another good column to index.
Now it's not unheard of. Two missing index in the design process and need to add it later. So, for example, maybe you didn't realize that the phone column was going to be an important search field. And after the implementation of the application, you realize that lot of users are searching for phone numbers.
Well, it shouldn't be too big of a deal to go add that index later and
help speed up the performance.
So if you run across the database application that has slow performance that involves a large data table, it's a good idea to check the indexes and check what the front end users are searching for. Now, when you go to create indexes,
you should understand why a column is being made in index. You do not just make every column and index.
If it's not a search parameter, there probably should not be in index
index structure and impact can vary by database engine. For example, my sequel requires a clustered index, while Microsoft sequel does not
with that said, Let's move in and implement some indexes.
Hi, so let's get some indexes implemented. I have some files open from our database Migrations folder and that is the building stable. The rooms table, the courses table, the users table and the user types table. We're gonna index the title and the name and the the files. And I have copied the code for that
from the Larible Documentation on creating indexes,
which I found simply by searching level migration. Create index
way you go. That was not the right column name, so I'm just gonna go ahead and
change it to the right one. We just want the title,
and we can tell that this is in an array because of the square brackets. But we only have one here.
Of course you want to get the name right. You want to make sure you remember that semicolon and you won't remember to go ahead and put it in an array bracket.
Otherwise, you will get in there when you run that migration. So let's go in and take that
and moved to the next one. After we save, of course,
And this one, we're gonna go ahead and index
the first name and the last name.
So last name
and first name.
And let's go ahead and
do the phone number as well. That's a common way to try and search for user records,
right? Save that one.
Going to the courses table,
Go to the bottom here.
That's not quite over where I'd like it. Now, the spacing doesn't matter. It would run if we left it over here. I just like to line it up
to make it look a neater.
So of course, code is gonna be one.
People typically search for things by code when there is a code available.
And then, of course, the title as well.
And let's go ahead and say that and move over
honestly, right there,
X. And here we have name. So we're going to go ahead and just index the name.
Spell it right it's important and save
and go over one
on dumbed also do city and state. How about that
Okay, save that.
Now What we're going to do is we're going to rerun our migration, and then we're also going to see the database again to make sure everything is working appropriately. So we need to get into our
vagrant machines. I'm gonna clear the screen with a c l e a r command.
And I'm going Teoh type
Okay, we're in, So I'm gonna see me to the right directory. Larible.
I'm going to switch to the Ruutel. Are they the root user with a pseudo dash? Ask command
and let's go ahead and type in PHP artists in my great fresh
And it looks like that worked.
So let's do a PHP artisan
Okay, it looks like that completed. Let's check our database to see that our indexes and whatnot is in there. So don't say that
and we have a We did it on our buildings,
So let's refresh this. Let's make sure we got the most recent data we dio properties
and X is.
And here we go Name City State. So we have those indexed. So there's our indexes.
Let's go ahead and head over to the summary.
So that brings us to the 5.1 indexes summary. What do we do in this lesson? What we discussed, what indexes provide us. And we also implemented a few in our database migrations and just to recap, indexes improved data operations speed. The database engine can jump across sections of the database table when looking for data.
When you do not have an index, the engine must scan record by record. Now, something to keep in mind here is this could still seem quick on small data tables. And then again, we implemented a couple new indexes on our name columns and likely that users would search for data by name. So that completes this lesson,
and I hope to see you the next