Original subtitles

0 Hi everyone 1

In this video we will learn about indexes 2

Indexes are a performance tuning method 3

Which allow faster retrieval of records 4

An index in a database is similar to an index in a book 5

Just like in a book if you want to find a particular chapter 6

or a particular topic 7

You first refer to the index 8

Find out the page number go to that page and look at that topic 9

similar is in the database 10

If there is an index 11

The database will first go to the index 12

Find out the relevant position of that record and then go to the table and retrieve the data 13

There are benefits of having an index and there are cons also 14

so if it helps in faster retrieval of records 15

If you have an index 16

It will lead to a delay in the up-gradation of records when you update records in a table 17

So if there is an index built on a table 18

And you try to update the table using the update command 19

It will take more time than if there was no index 20

So it is important that we only 21

Apply indexes if it is really necessary 22

Let us learn how to apply an index 23

The syntax for that is 24

We write create 25

Index 26

Give the index a name 27

On the table name 28

and then we specify the column names which are to be used 29

For creating this index 30

Usually we have only one column name, this single column index is called as simple index 31

Sometimes we use two or more columns 32

as primary key to retrieve data, in those cases 33

we use more than two columns 34

And that index is called a composite index 35

Let us look at one example 36

So if you want to create an index 37

On month values 38

If you remember we had in the cross join we had a month table 39

On that month table if you wanted to create an index 40

You can create an index like create index 41

Mon underscore IDX 42

Usually when you are naming an index developers name an Index like this. It is not mandatory you can name it anything 43

But usually it is done like this, mon_idx 44

This idx give you an idea that it is an index 45

On 46

Month values this is the table name and in the bracket the column name is written 47

Let us go and write this in the PG admin 48

Create index mon index on month values this is the column name select this 49

We have 50

Successfully created an index. To check the index 51

We go to this particular table 52

so let us refresh and go to the particular table 53

open it. There is one 54

Index option there is your 55

Index mon index 56

So 57

This index is created whenever you 58

Do a query on this table 59

It will automatically go and use this index that is why whenever you will be updating this table also it will go and 60

Change this index also 61

That is why it'll be taking more time 62

Apart from creating you can also rename an index or drop an index. Let us look at the syntax for that also 63

So to drop an index you just write drop index 64

And give the index name 65

This if exists 66

Is an optional keyword if you write it it will check if the index is there and then delete it 67

If it is not there it will not give you an error 68

So when you are running 69

A set of queries 70

And this is in between them 71

If you have not return if exist and you are dropping an index and it does not exist 72

It will fail the query and your all the queries in your set of queries will not run 73

so it is advisable that you write if exists also 74

In between 75

And this if exists 76

Keyword can be used while dropping 77

Tables also. So it is not 78

Restricted to indexes, you can use it to 79

Drop table also 80

The Other optional keyword is the cascade and restrict 81

So if there is an 82

Database object which is dependent on this index and you're deleting this index 83

What will happen to that dependent object 84

So if you write cascade 85

That dependent object will also be deleted 86

If you write restrict 87

This index will not be deleted it will give you an error saying that there is an dependent object you cannot delete this 88

Index 89

So that is use of cascade and restrict keyboard 90

If you want to rename an index you will use alter index 91

Give the index name rename to give the new index name 92

So this is how we drop and rename an index 93

let us go and drop the index that we just created 94

so we'll just write drop index 95

and write the name of the index 96

The query ran successfully just refresh this table 97

And within indexes 98

There is no index 99

as of now 100

So we successfully deleted the index 101

You can rename using the syntax that we provided 102

Apart from this there are some good practices while assigning an index 103

So it is advisable that 104

The column that you are using for creating index is of integer type this will be taking less space and will be 105

More efficient 106

So even if 107

You have 108

A text type of column which you want to use 109

You can 110

Do a one to one mapping of that textual column to an integer column and then use that integer column as an index 111

So that will be more efficient 112

another important point is make sure that the column you are building 113

Is declared not null. so if there are null values 114

The index does not work as efficiently 115

And it is very important that you build an index when it is really necessary otherwise 116

Some of the queries 117

On your database will perform less efficiently 118

That's all for the index 119

In the next video we will learn about some important string functions