This week's book giveaways are in the Refactoring and Agile forums. We're giving away four copies each of Re-engineering Legacy Software and Docker in Action and have the authors on-line! See this thread and this one for details.
I have about 8K rows in a big table which wasn't originally indexed. I added 3 indexed and the performance improved drastically. My questions is, will these indexes affect the future insertions into this table? Thx.
posted 10 years ago
Indexes effectively transfer work from output time to input time, so the answer to your question is yes, the indexes will slow down inserts. It is always a trade-off, and the ideal solution depends on your application. If there are lots of inserts, and they they have to be lightning fast, you might be better of with no indexes and slow queries, but that would obviously be an unusual situation. In most situations the inserts with indexes will be ok.
There are even situations where it is best to speed up inserts by minimizing the number of indexes, but come reporting time you build a bunch of indexes, run your reports, and then drop the indexes again.