All language subtitles for 2. INDEX

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