The joy of every developer of an application to see what he build is providing value to the end users, there are a lot of layers of things that happen when a user makes a request on that Application, today we are going to talk about database indexing, a powerful technique that helps speed up queries around fetching data from the database.
An index is something you create in a column of a table to speed up searches involving that column.
Indexes are a powerful tool used in the background of a database to speed up querying. Indexes power queries by providing a method to quickly lookup the requested data
Indexes contain all the necessary information needed to access items quickly and efficiently
Indexes serve as lookup tables to efficiently store data for quicker retrieval.
Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against. And not updated
There are two main types of column index
Clustered index
Non-clustered index
Clustered Index
The record is organized in secondary storage in some kind of order. A Table can only have one clustered index because the record can only be in one particular order.
However, a single clustered index can be made up of multiple columns, in what we called a composite index.
If a table has a primary key, this will normally be the clustered index.
When a Query involving a clustered index is executed, the data in the secondary storage can be searched very quickly.
Non-clustered index
This index makes use of a data structure that is different from the main record. Each value in the index has a pointer to the physical location of the entire record on secondary storage.
When a query involving the index column is executed, a copy of the entire non-clustered index is loaded into memory.
then a search for a match is run, when a match is found, the entire record Is retrieved from secondary storage.
A table can have many non-clustered indexes. So you can index as many different columns of a table as you like, but whenever you change, add, or delete data, each index on disk has to be updated immediately, Data modification is slow in non-clustered indexes due to this.