2

I heard that "Lesser the number of indexes means faster inserts, updates and deletes". Is this correct? If yes, then why?

Mat
  • 202,337
  • 40
  • 393
  • 406
david blaine
  • 5,683
  • 12
  • 46
  • 55
  • 1
    Did you read the SO rules? – Dan Bracuk Apr 20 '13 at 20:14
  • I don't know if you're using Oracle, but [this thread](http://stackoverflow.com/questions/141232/how-many-database-indexes-is-too-many) is a pretty good read. – Paul Apr 20 '13 at 20:18
  • at least he got his answer before the question got closed. – Dan Bracuk Apr 20 '13 at 20:40
  • @DanBracuk - Is there any way i could have improved this question or is it just "bad"? Perhaps this would make it worth re-opening - So an index is just a way to sort data in a column and keep that sort order handy for quickly accessing the column elements ? If we update a non-indexed column, then performance should not be affected, right ? – david blaine Apr 20 '13 at 20:42
  • It's not a programming question. You earlier asked if your question broke the rules, and it's still not clear whether you have not read them or simply interpreted them differently than I did. The 5 people who closed your question seem to share my interpretation. – Dan Bracuk Apr 20 '13 at 21:02

2 Answers2

12

Yes it's correct.

Indexes make select queries run faster. However, whenever you write you your tables, the indexes have to be updated as well, and that takes time.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • 1
    Just to add: obviously the more indexes you have, the slower the process becomes. – Paul Apr 20 '13 at 20:14
  • 1
    only makes select queries run faster of the where clause has columns that have indexes ... – tgkprog Apr 20 '13 at 20:16
  • https://www.google.co.in/search?q=sql%20index%20how%20do%20they%20help%20select%20queries? replace sql with your database name like my-sql – tgkprog Apr 20 '13 at 20:18
  • So an index is just a way to sort data in a column and keep that sort order handy for quickly accessing the column elements ? If we update a non-indexed column, then performance should not be affected, right ? – david blaine Apr 20 '13 at 20:20
  • 1
    @davidblaine: for MVCC databases (Postgres, MySQL/Innodb and some others), index would have to be updated even if you update non-indexed column – mvp Apr 20 '13 at 20:24
  • @mvp - where can i read about that ? – david blaine Apr 20 '13 at 20:40
  • You have a typo, I guess, _whenever you write __to__ your tables_. – potashin Jul 17 '15 at 02:13
3

YES

It is important to know how the indexes work.

The indexes (you create) are secondary and most often using B+ trees or something similar. The values in these trees point to records you have in your database so the SELECT clauses using the indexed columns are faster as they can be found quickly in the tree (faster than just checking each line) but the update, insert and delete in the database needs also the indexes to be updated which slows the process.

kuncajs
  • 1,096
  • 1
  • 9
  • 20