WEBVTT

00:00.000 --> 00:05.000
Indexing and Query Optimization

00:05.000 --> 00:07.000
Learning Objectives

00:07.000 --> 00:14.000
Understand what database indexes are and how they improve data retrieval.

00:14.000 --> 00:20.000
Differentiate between clustered and non-clustered indexes.

00:20.000 --> 00:27.000
Apply basic query optimization techniques to improve SQL performance.

00:27.000 --> 00:30.000
Understanding indexes

00:30.000 --> 00:35.000
In SQL, searching for specific data in a large table can be slow,

00:35.000 --> 00:40.000
like checking every student record one by one to find a name.

00:40.000 --> 00:42.000
That's where indexing come in.

00:42.000 --> 00:47.000
An index is a special data structure that helps the database find information faster,

00:47.000 --> 00:50.000
just like the index from a textbook.

00:50.000 --> 00:53.000
It improves the speed of select queries but has a trade-off.

00:53.000 --> 00:57.000
It makes insert, update, and delete operations slightly slower

00:57.000 --> 01:00.000
because the index must also be updated.

01:00.000 --> 01:05.000
So, indexes help us read faster but may slow down writing a little.

01:05.000 --> 01:10.000
A useful balance when we need quick searches.

01:10.000 --> 01:13.000
Creating and dropping indexes

01:13.000 --> 01:17.000
In SQL, we can create an index on columns that are searched often,

01:17.000 --> 01:20.000
such as student names or marks.

01:20.000 --> 01:25.000
For example, we start by viewing all data with select from student.

01:25.000 --> 01:29.000
A search-like select star from student where ID equals 49.

01:29.000 --> 01:31.000
It can be slow.

01:31.000 --> 01:36.000
Without an index, the database must scan the entire table row by row to find a match.

01:36.000 --> 01:41.000
To speed it up, we create an index using Create Index IDX,

01:41.000 --> 01:43.000
Student ID on Student ID.

01:43.000 --> 01:48.000
Here, as we can see, it creates an index called IDX Student ID.

01:49.000 --> 01:54.000
Now, running select star from student where ID equals 49.

01:54.000 --> 01:59.000
It gives result much faster as the database can find the result directly using the index

01:59.000 --> 02:02.000
without scanning the entire table.

02:02.000 --> 02:05.000
Clustered versus non-clustered indexes.

02:05.000 --> 02:09.000
In SQL, indexes are mainly of two types,

02:09.000 --> 02:11.000
Clustered and non-clustered indexes.

02:11.000 --> 02:17.000
For example, on the left, the Clustered Index shelf shows books arranged in order of their price.

02:17.000 --> 02:21.000
198, 93, and so on.

02:21.000 --> 02:25.000
Here, the data itself is stored in that order.

02:25.000 --> 02:29.000
On the right, the non-clustered index shelf has books in random order,

02:29.000 --> 02:35.000
but a separate list tells us where each price is, like 98 rupees for the gray book,

02:35.000 --> 02:40.000
93 rupees for the white book, and 80 rupees for the blue one.

02:40.000 --> 02:45.000
So, a Clustered Index keeps data sorted while a non-clustered index uses a list

02:45.000 --> 02:48.000
to point to where the data is.

02:48.000 --> 02:51.000
Query optimization techniques.

02:51.000 --> 02:56.000
In SQL, query optimization means finding the fastest way to get the right data.

02:56.000 --> 02:59.000
Imagine a student searching through a huge library.

02:59.000 --> 03:03.000
One way is to check every book one by one, but that takes forever.

03:03.000 --> 03:08.000
Instead, with a proper map of library, the student can go directly to the right shelf.

03:08.000 --> 03:11.000
That's what SQL optimization does.

03:12.000 --> 03:16.000
When we have more than one lack records, performance becomes important.

03:16.000 --> 03:21.000
Developers use explain or explain analyze to see how the query is actually running.

03:21.000 --> 03:25.000
In our example, the result shows a table scan.

03:25.000 --> 03:30.000
This means the database checks every row one by one, which is comparatively slower.

03:30.000 --> 03:34.000
From this, we understand where the query is taking time.

03:34.000 --> 03:38.000
Then we can decide where to add indexes to improve speed.

03:41.000 --> 03:47.000
Indexes in SQL work like shortcuts that make searching data faster and more efficient.

03:47.000 --> 03:52.000
They help the database locate rows quickly, saving time during data retrieval.

03:52.000 --> 03:56.000
By understanding how to create indexes and apply optimization techniques,

03:56.000 --> 04:00.000
we can make our SQL queries run smoothly.

04:00.000 --> 04:05.000
Just like good organization helps us study faster and smarter.