Day 24: Indexes In SQL

Day 24 Of Learning

Image By Author

Hey guys, hope you’re good.

Today, we’re gonna be looking at indexes in SQL.

So, let’s get started

What are Indexes in SQL?

Indexes in SQL are special lookup tables that improve the speed of data retrieval operations on a database table. They are used to quickly locate rows without having to scan the entire table. An index is created on one or more columns of a table.

Why Are Indexes Used?

Indexes are used to enhance query performance by reducing the time required to fetch records. Without an index, SQL searches the entire table (a full table scan) to retrieve data. With an index, SQL can efficiently find the required data with fewer operations.

Types of Indexes in SQL

1. Primary Index – Automatically created when a PRIMARY KEY is defined.

2. Unique Index – Ensures that all values in a column are unique.

3. Clustered Index – Sorts and stores the data rows in order based on the indexed column (each table can have only one).

4. Non-clustered Index – Stores a separate structure for the index and has pointers to the actual data.

5. Composite Index – An index on multiple columns to speed up queries that filter by multiple conditions.

6. Full-Text Index – Used for searching text-based data efficiently.

Image By Author

I have created an index on my title column and when I use my condition of ‘Grand Theft Auto V’ the query instead of scanning the whole table, focuses on the indexes matching with the condition, in return using less processing time.

Exercise

1. Load your train station dataset

2. Create indexes in departure station

   ---

   I am a BI Engineer working at a Data And AI Firm. I’m trying to help newbies in Data land their first data job and impact as many fellow data professionals as I can. Here’s my contact info:

   1. ajmal7809@gmail.com

   2. Linkedin

   3. bideveloper_ (Discord)