SQL: What is Indexing?

•

SQL: What is Indexing? 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.

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.

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.

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.

3. How to Define an Index (in Postgresql): "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.

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.

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: twitter.com/vikasrajputin/status/1517330102400344065

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/vikasrajputin/101

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