What is Indexing?
Indexing is a procedure introduced for database operations and other queries (received by CPU) are optimized by reducing the amount of time needed to complete a query, indexing helps optimize queries and other database processes while fetching data in lesser time. The indexes are stored using the B-tree data structure. Only utilize indexing if the data is massive and the application requires a lot of reading. Indexing may slow down write operations if an application is write-intensive.
Structure of an Index in a Database
How indexing helps in reducing costs?
The data is fetched from secondary memory in blocks as studied in OS. Note here data can be structured or non-structured. At the backend, the storage manager in OS does the job by putting all the data from logical drives into secondary memory in multiple chunks of blocks known as sectors. Now, these blocks are fetched into main memory(RAM) where we are not focusing on reducing the block size or changing it.
Here in indexing when a single block is inserted in RAM at once and retrieval of data occurs. If the answer to the desired query is fetched then it is called ‘cache-hit’ else it is known as ‘cache=miss’.
Tip: There is a cost associated with this cache hit-miss operation known as I/O cost.
In this way instead of rolling the complete data into RAM which takes an enormous amount of time as a cost in designing the model, with indexing the same data can be fetched just likely what we have had in books.
Illustration:
SELECT * from GFGEmployees where Name = Mayank Solanki;
Now if we do not apply to index we have to put the whole record from the organization directory to RAM for just a basic query while with help of indexing no matter how big the organization scales up cache hit-and-miss operations drastically reduce the cost not only for this query but even to complex ones as there will be only hit or miss operation. If fetched, hit else cache-miss.
Example:
Indexing in a Table In a Database
Note: Indexing and hashing in a database are very important from an interview perspective as it is been asked in many system design interview.
Advantages of Indexing
Indexing has several benefits, including
- Faster SELECT queries.
- Makes a row distinctive or helps to eliminate duplicates from a row.
- We can search against huge string values for full-text indexes, such as locating the string for a substring.
Disadvantages of Indexing
- The update process is speed up if the where conditions refer to an indexed field, but the Insert, Update, and Deletes query is slowed down because the index must also be updated while updating.
- Extra room is needed for the indexing parts.
Now after having an understanding of the concept of indexing let us adhere forward to the concept of partitioning.
Complete Reference to Databases in Designing Systems – Learn System Design
Previous Parts of this System Design Tutorial
Contact Us