Let me take you through an example. Imagine that you are in a shop to purchase a book on TCP/IP. And you found one suitable book that contains all the information related to TCP/IP. The Shop keeper will never allow you to sit there and read the entire book!!. So what most of us do is to search for topics of our interest in that book.
How will you search a topic of your interest from a book for TCP/IP, that consists of 1000 pages. If you sit there and turn pages one by one, am sure that the shopkeeper will surely pat your back in some time. This is the main reason why authors implement "Table of content" or sometimes called an "Index". Most of the books do keep a couple of pages in the beginning reserved for Index. You can easily search your topic of interest from the table of contents, and directly reach the exact page number of your topic.
A database index is somewhat similar to this table of contents in a book. Indexing will help a data base query to be retrieved fast (Because the query does not require to go through the entire table to get the data, but it will find the data blocks from the index.).
From the above shown example database table, you can clearly understand what is a record and what is a field. Suppose if the above database is a big one with 1 Lakh records which means each filed will have one lakh values.
Now if i want to search something out of that 1 lakh fields, a linear search will be take too long to complete, but what about a binary search(binary search is a fast searching algorithm, when you have a sorted list of values, either ascending or descending order.)
Why insertion is slower when you have a INDEX in a table?
It's because the database server has to do calculations each and every time you insert a new row. Basically, you end up re-indexing the table each time. It doesn't seem like a very expensive operation, and it's not, but when you do that many of them together, you start to see the impact. That's why you usually want to index after you've populated your rows, since it will just be a one-time cost.
If you have an index on a table, as you add data to the table SQL Server will have to re-order the table to make room in the appropriate place for the new records. If you're adding a lot of data, it will have to reorder it over and over again.
Of course, if you are importing the records in index order it shouldn't matter so much.
- Indexing each and every field in a table will reduce the write performance.
- Indexing a field with unique values in a table is advisable
- Fields that act as a join in relational database must be indexed, as they helps in complex queries across multiple tables.
- Indexing also uses disk space so be careful in selecting the fields you want to index
Key points to note about indexes in database
- Indexes in database contains only sorted values, due to which searching a particular value in a field becomes faster
- Binary search algorithm is used to search from index which makes the searching even faster
- Database queries containing joins becomes much faster with indexing
- Disk Input Output operations can be reduced with the help of indexing in database
- If suppose there is a query that only retrieves data from an indexed field, the database server will only access index without accessing the complete data in the row.
- Indexing large number of fields in a table will make write operation slow
NOTE: You should NEVER EVER create an index on an empty table if you are going to massively load it right afterwards.