SQL: What is Indexing?
a thread...
a thread...
1. Definition
It is a technique, using which we can improve search performance in our database.
Helps in quickly searching the records in the table.
It is a technique, using which we can improve search performance in our database.
Helps in quickly searching the records in the table.
2. Example:
You've mostly seen the books with an Index page,
where multiple pages are grouped into one chapter and list them.
This allows us to skip unnecessary pages/chapters and go to our favourite page/chapter quickly.
It's nothing but indexing.
You've mostly seen the books with an Index page,
where multiple pages are grouped into one chapter and list them.
This allows us to skip unnecessary pages/chapters and go to our favourite page/chapter quickly.
It's nothing but indexing.
Similarly,
When we search any record in DB, it goes to the index and finds the exact location of the record and skips the unnecessary records in between and quickly gives us the results.
When we search any record in DB, it goes to the index and finds the exact location of the record and skips the unnecessary records in between and quickly gives us the results.
Note:
Different DB vendors have introduced their own types of Index.
Many DB has even more than one type of Index. But more or less their purpose is the same.
The query syntax to define the index may also vary based on the DB vendor.
Different DB vendors have introduced their own types of Index.
Many DB has even more than one type of Index. But more or less their purpose is the same.
The query syntax to define the index may also vary based on the DB vendor.
3. How to Define an Index (in Postgresql):
"CREATE INDEX <IndexName> ON <Table>(<Column>);"
Where,
Table = STUDENT
Column = ROLL_NO
Code Snippet:
"CREATE INDEX <IndexName> ON <Table>(<Column>);"
Where,
Table = STUDENT
Column = ROLL_NO
Code Snippet:
4. When to use Indexing
Always remember Indexing should be only applied to the column which is frequently being used in the where clause.
Indexing works great on columns which contain numeric data.
Always remember Indexing should be only applied to the column which is frequently being used in the where clause.
Indexing works great on columns which contain numeric data.
Indexing on primary key columns is not required because most modern databases implicitly apply indexing to them.
5. When not to use Indexing
If DB has fewer records no need to use indexing. it will overkill the purpose of Indexing.
Try to avoid Indexing on a column containing long text-based data, in such case Indexing will be quite poor.
If DB has fewer records no need to use indexing. it will overkill the purpose of Indexing.
Try to avoid Indexing on a column containing long text-based data, in such case Indexing will be quite poor.
Don't apply it on every column, otherwise, this will degrade the write and update operation on DB. As every write and update operation also updates the Index.
That's it for now!
If you enjoy reading this thread then you might also like my previous thread:
If you enjoy reading this thread then you might also like my previous thread:
Small Announcement! 📢
I've opened my calendar for my audience and love to interact with you over 1:1 sessions on:
1. Career Guidance/Mentorship
2. Candid Talks
3. Content Creation
For more details visit:
calendly.com
I've opened my calendar for my audience and love to interact with you over 1:1 sessions on:
1. Career Guidance/Mentorship
2. Candid Talks
3. Content Creation
For more details visit:
calendly.com
Namaste, I'm Vikas!
I write a thread every Mon, Wed & Fri on
Java, Javascript & Fullstack Development.
To read all my future threads follow @vikasrajputin
I write a thread every Mon, Wed & Fri on
Java, Javascript & Fullstack Development.
To read all my future threads follow @vikasrajputin
جاري تحميل الاقتراحات...