What Are The Advantages Of Using Database Index?


1 Answers

WealthNet Partners Profile
So without an index your data is essentially stored on a chronological basis according to when it was entered into the system. Suppose you have a database of a million names, 50 of whom have the last name Smith and only 1 whose name is Joe Smith, but there are 100 other Joes with different last names. Without an index, if you do a query for people with the last name smith, the database will have to look through all 1 million records to find 50 that are relevant. Similarly, if you do a search for people with first name Joe and last name Smith, the database will search through all million results to find anyone with the first name Joe, and then check all 101 of those results for people with the last name smith.

An index is essentially a copy of the table, but sorted in whatever order you specify. By having even only 1 index for either first name or last name, the database is able to jump to the relevant results, ruling instantly ruling out over 99.99% of the other results, and thus saving tons of time.

The downside of indexes is that each one essentially takes up as much space as the original table. But generally the speed benefit outweighs the space issue, especially considering the low cost of memories these days.

Answer Question