Table Indexes

[Joetoben]

How do Indexes "speed up searches" but "slow down updates"? I see how to create an index, and I see that it may exclude duplicates, but what is the utility of the index? What is the purpose aside from excluding duplicates?

[ItsMe]

...what is the utility of the index? What is the purpose aside from excluding duplicates?

I do not know if I have a perfect answer but I like to index fields to speed up select queries. Any field that is a Key value will likely get an index. So, all PK's, all FK's, and most Business Keys. I may have a query that has a JOIN on a business key and this would be a situation that may benefit from indexing.

After all of my PK's and FK's are indexed, I will identify Business Keys. My understanding of a Business Key is a value within a field that is recognizable to the User as a key or unique value. Some examples of Business Keys are Driver License numbers, Invoice numbers, vehicle VIN's, Tax ID, Employee ID, etc. If I have a search field for any of these business keys, I will consider Indexing the field. The more indexes I create, especially of text fields, the more memory needed to update the index during updates. Because a given field is indexed, there is additional overhead when updating the field because the index needs to be updated also. Instead of updating just the field, you are also updating the index.

The advantage to having an up to date index is the index is used to locate a specific field (I believe it is a multidimensional array or a Dictionary Collection that considers both the column and row, but I do not know that for sure) within a table. It gets a serial number, so to speak, and this index is held in memory for immediate consumption.