Some Basic Rules for choosing to use Indexing or not

- Indexes slow down inserts and updates, so you should use them carefully on columns that are FREQUENTLY updated.

- Indexes speed up where clauses and order by.

Remember to think about HOW your data is going to be used when building your tables.

There are a few other things to remember. If your table is very small, it's worse to use an index than to leave it out and just let it do a table scan. Indexes really only come in handy with tables that have a lot of rows.

Why indexes may hurt performance of mysql DB incase of INSERT statements?

Indexes slow down INSERTS because of the overhead required to update the index, but an update might not hurt depending on whether the update is changing the index or not.

Updates that don't change indexes won't incur that extra overhead.

An update is actually a combination of a read and a write.  If the read uses the index and the update doesn't change it, the update might even be faster instead of slower.