Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

00:00
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.
00:15
So let's begin stepping through these bullet points
00:18
so the first bullet point improves operation speed uninvolved database tables,
00:24
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.
00:36
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.
00:50
So it is important to get those indexes on columns that make sense to really help improve that database performance,
00:58
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.
01:11
You're select columns that front end users would likely search data for
01:15
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
01:30
because typically your front and users would be searching for customers by name
01:36
or by phone number, so phone number might be another good column to index.
01:41
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.
01:59
Well, it shouldn't be too big of a deal to go add that index later and
02:02
help speed up the performance.
02:06
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,
02:20
you should understand why a column is being made in index. You do not just make every column and index.
02:25
If it's not a search parameter, there probably should not be in index
02:30
index structure and impact can vary by database engine. For example, my sequel requires a clustered index, while Microsoft sequel does not
02:42
with that said, Let's move in and implement some indexes.
02:46
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
03:06
from the Larible Documentation on creating indexes,
03:08
which I found simply by searching level migration. Create index
03:14
way you go. That was not the right column name, so I'm just gonna go ahead and
03:20
change it to the right one. We just want the title,
03:23
and we can tell that this is in an array because of the square brackets. But we only have one here.
03:30
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.
03:38
Otherwise, you will get in there when you run that migration. So let's go in and take that
03:43
and moved to the next one. After we save, of course,
03:46
little disk.
03:49
And this one, we're gonna go ahead and index
03:52
the first name and the last name.
03:57
So last name
04:00
and first name.
04:03
And let's go ahead and
04:06
do the phone number as well. That's a common way to try and search for user records,
04:12
right? Save that one.
04:15
Going to the courses table,
04:17
Go to the bottom here.
04:21
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
04:30
to make it look a neater.
04:31
So of course, code is gonna be one.
04:34
People typically search for things by code when there is a code available.
04:41
And then, of course, the title as well.
04:46
All right.
04:48
And let's go ahead and say that and move over
04:54
and
04:57
honestly, right there,
05:00
X. And here we have name. So we're going to go ahead and just index the name.
05:09
Spell it right it's important and save
05:13
and go over one
05:15
on and
05:17
name
05:20
on dumbed also do city and state. How about that
05:27
city
05:30
on states?
05:31
Okay, save that.
05:33
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
05:45
vagrant machines. I'm gonna clear the screen with a c l e a r command.
05:50
And I'm going Teoh type
05:54
bag grants.
05:56
Ssh!
05:58
Okay, we're in, So I'm gonna see me to the right directory. Larible.
06:02
I'm going to switch to the Ruutel. Are they the root user with a pseudo dash? Ask command
06:09
and let's go ahead and type in PHP artists in my great fresh
06:19
And it looks like that worked.
06:21
So let's do a PHP artisan
06:25
DVC.
06:29
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
06:39
and we have a We did it on our buildings,
06:44
So let's refresh this. Let's make sure we got the most recent data we dio properties
06:48
and X is.
06:53
And here we go Name City State. So we have those indexed. So there's our indexes.
06:58
Let's go ahead and head over to the summary.
07:01
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.
07:20
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,
07:40
and I hope to see you the next

Up Next

Intermediate SQL

This free course introduces the student to intermediate concepts found in the implementation and application of Structured Query Language (SQL) within professional business environments.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor