The title sums it up.
I've learned and always heard that indexes in tables improve CRUD operations. A developer that I met last weekend told me that he does not like Indexes because they are bad – yes, "bad" does not clarify anything but we did not had time to discuss it further (we were at a party).
Anyway, maybe because my lack of experience, I do not know of a scenario where Indexes can cause troubles during CRUD operations, but maybe there are a few out there. I'm asking this question to know if there are any…
Best Answer
Well, I think you have some mixed concepts:
An index improves performance of READ OPERATIONS ( those of SELECT ) while increase the processing time of INSERT/UPDATE OPERATIONS ( So they don't improve all CRUD operations, as you've heard ). As each time you insert a new row, you should update the index, if you have too much indexes you are increasing the time of insertions, and sometimes also the updates ( if the update involves something indexed.
An index uses space, a lot of space if you have a lot of rows.
It's not a problem for the system to know which is the best index to use, I think this is not a real performance killer, but you should look for redundant indexes, as they're using space and time of inserts / updates.
For this, you should know how your DB engine works with indexes, in MySQL if you have an index over name, surname fields and other over name, this latter is redundant, as it's included in the first one ( because it appears in the same order, surname only is not included ), as an example of redundant indexes.
Also, you should test how your DB is going to interpret the query and which indexes are going to be used ( in MySQL, you can use Explain... and the query you are testing )
Finally, indexes are one of the most important features of databases, indexing can't be 'bad' by itself, and normally the problem appears when you forget to add some particular index, and not the excess of indexes, but it could happen.