DEV Community

Cover image for What exactly does Indexing in a Database do?
Preethi Vuchuru27916
Preethi Vuchuru27916

Posted on

What exactly does Indexing in a Database do?

Hey there! While working with the database, I came to know how important it is to use index on a table which holds a large number of records. In this article, I will be explaining
what difference does it make to add an index to a table.

Consider the below student table is created in Oracle SQL Database.

Alt Text

Now I want to get the details of the students who are in Primary Grade. Obviously I need to write a select query.

Select * from student where student_grade = “Primary”;

Now what happens?

Internally it identifies the column student_grade and searches sequentially all the records which match Primary.

It finds the first record matching primary and returns the row. It finds the second record matching primary and returns the row. It continues its process of finding Primary. Skips third record and finds fourth record matching primary. Now does it stop? No. It still continues to go till the end of the table searching for primary.

It will look fine for a table with just 5 rows but what if I have a table with 10000 records.

Alt Text

Lot of time gets involved and who would respect such a time delay?

Here is where our Indexing comes to save the time. We are all aware of what indexing means. It keeps references for quicker access. A cliche example would be a index of a textbook keeping page references to topics covered in the book.

Now lets create a index on the column Student_grade and see what happens.

Create index student_student_grade_I on student(student_grade);

student_student_grade_I is the index_name.

With the addition of index on our table, a new data structure gets created which will hold the student_grade and a pointer that references the original record in the table. The Data Structure is BTree which offers the advantage that it has all the records sorted. A index would look similar to the below table on the left.

Alt Text

It now finds 2,3,4 records matching primary. Stops executing and returns the records with the help of record_pointer.

References

1)https://www.youtube.com/watch?v=aZjYr87r1b8
2)https://chartio.com/learn/databases/how-does-indexing-work/

Top comments (4)

Collapse
 
parsakafi profile image
Parsa Kafi

Nice 👍

Collapse
 
avinashdalvi_ profile image
Avinash Dalvi

Good learning.

Collapse
 
3ankur profile image
Ankur V

Very nice 👍

Some comments may only be visible to logged-in visitors. Sign in to view all comments.