Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

00:01
welcome back to modulate. This is less than 8.3 examined. And this lesson we're gonna take a look at some common index problems, and these include unused indexes, duplicate indexes and missing indexes were gonna look a query. Optimization is with explain to identify missing indexes.
00:20
And here are some things to avoid
00:22
with respect to indexes. You want to avoid using an index when the majority of table records will return. Now this is somewhat subjective,
00:32
but the use of an index becomes expensive, the more records you return. And that's because the database engine actually has to do some extra computation every time it examines the index. So not examining the index when a great majority of the records coming back well actually will actually
00:51
benefit performance.
00:52
Now the cut off point is a little bit subjective, but usually study 5% is a good 75% and upward is a good time to start considering not using the index. So if you have a table of ah 100 records and you are going to write a query that's going to return 80 records,
01:11
you may not want to use an index. In that case,
01:14
index use case strengthen as record return count decreases So the best case foreign indexes when you're returning one record
01:23
and excuse case weakens as record return count from the table increases. So the worst case of when you want to avoid an index is when you're gonna return every record from the table. If you're gonna bring back every record from the table, there's no need to use an index
01:40
now. That said, let's go ahead and jump into the development environment and take a look at what this looks like.
01:47
Here we are, the development environment. I have three queries on the script pain that are all selecting from thesis database. Now the system a base is a database that gets installed with my sequel, and it contains a table and a lot of views
02:00
that helped really diagnose potential problems in my sequel and provide a lot of useful information on your database.
02:07
Now there are more views than we can go through. As you can see on the left hand side, there are a lot of views,
02:14
but we're going to focus on three that will provide useful information related to our index problems. So if I run the first query, which is skim on used indexes.
02:24
It'll give us a list of unused indexes. So these air indexes that might be safe to remove from our database and remove that unnecessary index weight from our database engine.
02:36
However, there are some scenarios in which it unused index. It might make sense in one such scenario, perhaps a table was just recently created, and nothing's been written that uses the index yet, although it is planned down the road for later.
02:52
So in that scenario, you wouldn't want to remove the index now. On the other hand, if you're looking at a table that's been unchanged for three years and it has unused index is, well, very well could be safe to remove those indexes.
03:07
So moving on,
03:07
if we select from skim a redundant indexes,
03:12
we'll see a list of indexes that are mentioned more than once on table definitions. And in a lot of cases, you might see a composite key that contains a index as well as an index by itself. And sometimes that's done so that the my sequel, OPTIMIZER, will automatically select that index
03:31
without the need to manually select the index.
03:35
So again, depending on the context, a duplicate or redundant index could make sense.
03:42
However, again, if you know that the table has a redundant index, that's been instead, she aided by accident. It should be safe to remove
03:51
now, moving on
03:52
the way that we would find missing index as we would select from statements with full table scans. So these are going to be queries that in Stan Shih ated a full table scan when running.
04:03
So if I select from that,
04:06
we can see the queries that have been run against the database on, we can see that these are the queries that required a full table scam.
04:16
So depending on what is going on, you could identify. You could use this to identify and missing index. Or you do have to be careful because it could be a programmer. That's just writing some weird queries. They don't really warrant the creation of in an index. So you do have to kind of look at this and identify what's going on
04:36
and if there is a place to optimize by introducing a new index.
04:42
So these are These have been three views that help you identify different problems with indexes and are useful when trying to optimize your database.
04:51
Now let's go ahead and head over to the review before we close out this lesson.
04:57
And that brings us to 8.3 summary And this lesson. We discussed the cyst database, which is installed when my sequel is installed.
05:04
This database contains many views with useful diagnostic information. In fact, it contains too many views to go over. But we did go over three related to identifying possible problems with index is the 1st 1 was the system that skim a unused indexes. This contains a list of database indexes that have never been used.
05:24
Next on the list is this system skim a redundant indexes, which contains a list of duplicate in Texas.
05:31
And finally we had sister out statements with full table scans, which contains a list of queries that enacted a full table scan to complete.
05:41
Now, something to keep in mind when looking at this data is that sometimes it's some scenarios or some context makes sense for such an item to exist,
05:50
for example, and the unused index, it's possible that you have a new database table with new indexes that isn't in the process of being designed, so it's never been used. And that makes sense.
06:02
An example of full table scans. You might have a programmer who is simply testing out new queries and isn't too concerned with indexes, and you may not want to go out and try to create indexes to facilitate his experimentation.
06:17
Now, with that said, that completes this lesson, and I hope to see in the next thank you.

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