Beginner Guide to Indexing in SQL: 🧵👇
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
- And skips the unnecessary records in between and quickly gives us the results
2.1. 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>);"
- If, Table = STUDENT and Column = ROLL_NO
- Example:
"CREATE INDEX IDX_STUDENT_ROLL_NO ON STUDENT(ROLL_NO)"
- "CREATE INDEX <IndexName> ON <Table>(<Column>);"
- If, Table = STUDENT and Column = ROLL_NO
- Example:
"CREATE INDEX IDX_STUDENT_ROLL_NO ON STUDENT(ROLL_NO)"
4. How to Drop an Index (in Postgresql):
- "DROP INDEX <IndexName>"
- Example:
"DROP INDEX IDX_STUDENT_ROLL_NO"
- "DROP INDEX <IndexName>"
- Example:
"DROP INDEX IDX_STUDENT_ROLL_NO"
5. 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.
6. 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. Because every write and update operation also updates the Index
That's a wrap!
Every Mon, Wed & Friday - I tweet a thread on Backend Development.
Follow me
@vikasrajputin
to read all my future threads.
Every Mon, Wed & Friday - I tweet a thread on Backend Development.
Follow me
@vikasrajputin
to read all my future threads.
Before you go, do you know?
I've also started writing on LinkedIn
I share some exclusive content there, which I never share here.
Follow me on Linkedin to stay updated with Backend content:
linkedin.com
I've also started writing on LinkedIn
I share some exclusive content there, which I never share here.
Follow me on Linkedin to stay updated with Backend content:
linkedin.com
جاري تحميل الاقتراحات...