Question

How do database indexes work? Describe the technical process used by database indexes to improve efficiency of queries and reduce resource consumption on a database server during complex queries and table joins.

Answer 1

Database indexing is a method used to sort an unordered table that will maximize the query’s efficiency while searching. If we want to search for an entity in a database without indexing the query will have to search through every row until the matching entity met, in case of large tables with complex queries and table links it is not possible because it is a time-consuming process. This is where indexing is important indexing sets up the column we are searching in a sorted order to assist in optimizing query performance.

It works in a way that indexing helps the database to create a data structure. It creates a binary search tree to sort and search for conditions because look-ups, deletions, and insertions all can be done quickly in a binary search tree. When the index creates a data structure on a specific column no other column is stored in the data structure so that data size will not increase.

Database indexes also store pointers which are used to store reference information of the location. Using pointer we can go into a table to find the specific row where that pointer points to, it helps query performances in table joins. If the Query looks for the specific row in the index, the index refers to the pointer which gives the desired row.