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.

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.

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.

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/