Indexing databases

Question

if we make an index on the primary key of a table how would it increase performance ,,,,as if the desired record afer creating the index might turn up in the end of the index list!!

Answer 1

Modern databases always have an index on the primary key. Creating another one would -- at best -- do nothing to your performance.

Answer 2

I think you're mistaken in your understanding of how indexes work. They're not lists.

For introductory reading on indexes, see the Wikipedia article.

Answer 3

The index is typically sorted. The database can then use a binary search and other mechanisms to ensure finding the desired item is efficient. Additionally, a table uses the primary key as a clustered index (typically) - the data in the table is laid out in the same order as this index which means that searching for data by this index is even faster.

Answer 4

Once you create an index on a table, any actions on that table (inserts, updates & deletes) will also be made to the index, keeping the table and the index in sync.

So if a row is added after the index is created, it will be put in the index in the correct place.

If it's correct place IS at the end of the list, it's still faster because the database will use a Binary Search (or other optimised search algorithm) to find the item, resulting in far fewer reads than a full table scan.

Hope this helps,

Answer 5

Does this mean that without searching algos indexing would do no good????