3.19 Indexes

00:00

Hi. Welcome back to you. Module three sequel programming this is less than five tables were moving into sub lesson 5.6 indexes. So what is an index? And index is a very important feature off most database engines that will allow, said database engine to very quickly search for records

00:18

and this importance or this feature kidnap. The importance of this feature cannot be overstated.

00:24

As the application grows in size, some applications, if not properly indexed, will almost outright fail in the eye of the users. Now, something to realize is that when you make a column, a primary I D.

00:39

It becomes indexed.

00:42

Now you might be saying, Well, okay, that's good. I'm done. I haven't index to column. Well, not necessarily.

00:48

You have to think of how the users and how the programmers are going to be searching for data. For example, consider a call recording program that stores the call recorded calls in a database. Well, they're gonna have an I D field,

01:02

but what are users going to use to search for calls?

01:07

And you probably guessed right, the phone number, they're going to search by the phone number. So in such an example, would be a very, very good idea to make sure the phone number is indexed. I once worked with a call recording application that recorded about 10 to 30,000 calls a day. So within a year you would have

01:26

a few 1,000,000 recorded phone call records to search through

01:30

and, if not properly, index

01:32

the database could take is long as

01:36

15 to 30 minutes, depending on the computer hardware to search for a phone call. And that would just be unacceptable for that. When properly indexed, that same search can resolve in

01:48

a second or two, maybe less, very quick. So how do indexes work? Well, indexes can get complicated. They can rely on data structures to go be on the scope of this course, such as binary trees.

02:00

But one easy example. Oven away. An index could work that really highlights how fast it can cut down the search. Time is, for example, consider a table where you have 10 million phone calls

02:13

Now what is a phone number? It's a number so we can put those in ascending order. So let's say we put those phone calls in ascending order. Now let's say we have to search those timid and calls for one phone number.

02:24

What can we do? We can say, Okay, let's go to the middle of that power. Love the five million Call.

02:31

And let's ask if this phone number is less than or equal to that phone number. That's in that five million. It's a parole, that five million rope.

02:39

And if it's true or false, we can immediately cut off the bottom or top five million. So what have we done in that one comparison? By being able to jump to the middle? Because we know that those are in ascending order.

02:53

We were able to immediately dismiss five million of the records and cut it down from 10 million to five million. If you don't have an index, the database is forced to search every record row by row so he can't cut out five million. It has to search those five million.

03:08

Now, if you get very, very lucky and you happen to be, you know, it positioned one for records and that happens to be the phone number you're searching for, then you know, it might appear to be very quick, but if you get unlucky and you have to search for

03:23

Ah, the phone call at the nine millionth record. You're probably going to be waiting a while now. How do we establish an index

03:30

in this database? Was very easy.

03:32

For example, let's go to the dogs table. Now we know that the idea is the primary key and its index. But what other

03:39

comb might we need to search by?

03:43

Well, we have very few comes so honestly,

03:46

we might need to search by all of these. In fact, I would probably index all of these, but we're not going to one. That's obvious

03:53

is the person I d. So let's go ahead and make that an index now. Andi Beaver. We'll just click on the index tab.

04:00

We can see that the primary key and the foreign keys are already indexed,

04:05

so it already index the person I D

04:08

and index the idea, which is good.

04:10

Those were kind of the obvious indexes. So if you set up your foreign keys on your part, MCI's you're already getting a lot of your index thing done.

04:17

So let's create a new index, though

04:19

let's say breed because we want to be ableto quickly. Search for different breeds.

04:25

Um,

04:26

we're not gonna mess with any of the different types. Binary tree or be tree is usually good enough and we're going ahead and click. Okay,

04:35

We're gonna save that,

04:39

and we can see the sequel code that creates the index. It's not very complicated by itself. It's great. Index on

04:45

Create Index. The index name using the the object type, which will be be treated, stands for buying territory on database table name

04:54

on this column. Breathe

04:56

so I'll go ahead and click. Persist.

05:00

Now what the database has to do is have to go and put those in order so that it respects the rules off thea

05:05

object type.

05:08

So that can take a little bit if you already have a lot of records

05:12

in your database,

05:14

and now we can see that we have that

05:17

breed indexed

05:19

and we're done.

05:21

So that brings this listen to close. And this lesson we discuss indexes and their importance. They help keep an application speedy. If you see a slow application that uses the database and index is a very good place to start looking when trying to figure out why it might be running slow