Any technical guy in this world can write code logic for developing a software. But finally performance matters for any software. We are aware that Indexes in database plays a vital role to fetch the records quickly which in turn gives good performance. But that does not mean that creating Indexes will help every time. Sometimes the database’s performance may go down after creating Indexes. We should be careful while creating Indexes. We might have heard of two terms called index scan and index seek while working with Indexes.
Index scan reads the whole data to find matches in the table. The time taken for index scan to fetch the records is proportional to the number of rows in the table as it scans the entire table. Index scan is useful in rare cases like in small tables(with less number of records) or while fetching very less percentage of records from a big table.
Index seek follows B-Tree structure to fetch the records. The time taken for index seek to fetch the records is proportional to the number of matching records as it reads only matching records. While doing index seek, engine directly knows the exact location(address) of the required tuple(row) by using B-Tree traversal. It is more efficient way of fetching data from a database as it fetches the data without reading the entire table.