Why it is not recommended to create index on all attributes in databases?

Question

It is easy to create index on all attributes of any relation, Why it is not recommended to create index on all attributes ?

[Edit: please give ans in detail, all new answers are welcome.]

Answer 1

[PART 2]

Therefore Indexing helps in faster queries but it comes at a cost of more memory and more maintenance.

Now to the main question. When to do Indexing?

Whenever DBAs will see that we are getting more queries based on X attribute, they ll create indexes for that X attribute. Its always on need basis. Simply indexing all the attributes will be useless.

Another Note: When Items are sorted then binary search Log2(n) is much quicker, but searching for an item based on some attribute which is not indexed will take n attempts in worst case

Now since the basics is out of the way. Let's think again from DB perspective. Check out the best answer in here https://stackoverflow.com/questions/1108/how-does-database-indexing-work .

It nicely goes over
1) Why is it needed?
2) What is indexing?
3) How does it work?
4) When it should be used?

Answer 2

1. SPACE
Indexes use disk space, so it's not free

2. TIME - MAINTENANCE
every time you add or update a record, you have to recalculate your indexes and having indexes on all columns would take a lot of time and lead to bad performance.

Answer 3

[PART 1]
First can you help explain us on how it is " easy to create index on all attributes of any relation" or is it a typo?

Now when talking in general, why it is not recommended to create index on all attributes for a table.

Short Answer:
By Prokopios Poulimenos

Long Answer:
Think of a list of people with 2 fields, Name and Mobile No. This list is currently sorted lexicographically on Names. So when I say find me the number of Niobe, its easy and we look for names starting with "N" then "i" and so on.
But if I say find the name of the user with mobile No. 1415926535, its possible but painful.
Now suppose I started asking for Names based on Phone numbers more often, then it will make sense to create a separate list sorted based on Phone Numbers and corresponding names beside it.

Now we can quickly find the names from numbers.

But notice this.
We had to create a different list.(More paper)
We ll hv to sort the list everytime when a new contact is added.(More Effort)

Answer 4

Morpheus sir, this cleared all doubt 😀
Thank you 👍

Thats why the expected ans was Bigoh(Log2(n)) 😃 now it all makes sense 👍

Answer 5

Just index ones that you need (as in, base values which cannot be derived in any other way from other values) in order to save space in the database. Plus you need to make lesser changes