What are indexes in a relational database How are they benef

What are indexes in a relational database? How are they beneficial?

Solution

Index in a Database is very useful for us to Organize the Data in the Database.

For Example, Consider a Real-time Analogy you have 100 different tools like \"Nuts\" \"Bolts\" \"Screws\" which are shuffled in a box. wouldn\'t it be difficult for us to spot our the Screws Immediately? So what do we have to do?

we Separate the Screws in a box-A, Bolts in a Box-B and Nuts in a Box-c. So Box-A, Box-B, Box-C becomes the index of the tools. Similarly, in a Database, every different kind of data are organized with an index.

I hope you should have understood whey we should have a Database Index. Now let us check out an Example

Consider a Table Name called \"Employee\" with three columns \"Name\" \"Age\" \"Address\". Now Assume the Table has 100 number of data.

Now Let us assume we run a very simple Query

select * from employee

WHERE employee_Name = \"Raj\"

Case 1: what happens without an Index for the Table

Now Every Row is now checked for \"Raj\" where all the 100 entries are checked to know where all the \"Raj\" is present.

Note there can be repeated Employee name \"Raj\" present in the Table as it has 100 number of data. The database has to scan all the 100 rows every time for executing this query.

Let us check out a Real-time Analogy: Now you need to search for the word \"Raj\" in a Book which has 1000 Pages in it. Now you need to scan every word by word to find out where the name \"Raj\" is Present.

This Process is Time Consuming and has performance issues.

Case II - What Happens with Indexing

Now consider the Table is Indexed for a column. So during the Indexing an Index table is created to distinguish the similar set of data sets.

For Example, Consider 20 Rows have the Employee_name as Raj. Now for all the 20 different \"Raj\" an Index is mentioned. So if the Query is getting executed it is easier to get the Employee_name as Raj in a very faster Mannar.

In a Simple Analogy, the book of 1000 Pages contains an Index of PageNumber to show where all the name \"Raj\" is present so that we can search for the Name \"Raj\" in a very faster manner.

B trees are used for Indexing usually.

Address is very Important For Indexing