Index is very similar to bookmarks in many ways: such as, helps people look for the subject faster, pre-organized list of subject, prevent users to read the whole subject to find what’s needed.
Index ensures database performance as it prevents db to scan entire table.
Compare with the dictionary and imagine where all the words are out of order, and there’s no index with it. You will have to read an entire book to find the word you are looking for. The “word” can be on the front page, or at the last. Index puts the data in order and aid user to find the data they want. However, there are possibilty where it is misused. The consiquence of this leads to downgrade in performance which is worst than not using one.
Full Scan
Overhead on Insert, Delete, and update
pros
improves query speed
helps reducing a use of system resource
cons
Needs more storage to manage index
adds extra work to manage index
using index falsely can result in degrading performance
Good use case
Table with massive records
column that uses little to no Insert, Update, and Delete
column that uses JOIN, WHERE, or ORDER BY frequently
column that has little to no duplicate data
