Indexing in Databases: Unlocking the Power of Data Retrieval

10 min readOct 7, 2023

In an era characterised by the exponential growth of data and the increasing demand for efficient data retrieval, database indexing has emerged as an indispensable tool. It serves as the backbone for optimising data access and query performance, playing a pivotal role in the functionality of modern database systems. This comprehensive guide explores the world of database indexing, from fundamental concepts to advanced techniques, providing valuable insights into harnessing the true potential of your data.

What is an index in a database?

At its core, an index in a database is a data structure that enhances the speed of data retrieval operations on a table at the cost of additional writes and storage space. Imagine a library catalogue that lists books by title, author, and subject, allowing readers to quickly locate books of interest. Similarly, a database index serves as a meticulously organised reference, providing a shortcut to the desired data, and significantly reducing the time it takes to retrieve specific records.

Indexes are pivotal for performance optimisation in databases. They transform the often time-consuming full-table scans into lightning-fast direct lookups. Without indexes, databases would be forced to scan entire tables, consuming valuable time and computational resources. Indexes, therefore, enable databases to deliver speedy responses to queries, making them a cornerstone of efficient data management.

Types of Indexes

Primary Key Indexes: A primary key index ensures the uniqueness of records within a table, preventing duplicate entries. It is a fundamental element for maintaining data integrity. In a practical example, consider an online store where each product has a unique identifier. The primary key index ensures that no two products share the same identifier.
Unique Indexes: Unique indexes, as the name suggests, enforce the uniqueness of values in a specific column but unlike primary key indexes, they allow for NULL values. In the context of a social media platform, a unique index on the email address column ensures that each user has a distinct email address, but some users might not have provided one.
Non-Unique Indexes: Non-unique indexes, also known as secondary indexes, accelerate data retrieval without requiring uniqueness. Think of a library’s index of books by genre. Multiple books can belong to the same genre, making it a non-unique classification. Similarly, non-unique indexes in databases expedite access to non-unique data.
Clustered vs. Non-Clustered Indexes: Clustered indexes determine the physical order of data rows within a table. It’s akin to organising physical books in a library according to the Dewey Decimal Classification. Non-clustered indexes, on the other hand, provide an alternative lookup mechanism, similar to a library’s index at the end of a book.

Index Data Structures

B-Tree Indexes: B-Tree indexes are the workhorses of database indexing. They maintain a balanced tree structure, allowing for efficient data insertion and retrieval. Consider a phonebook; B-Tree indexing ensures quick surname-based lookups.
Hash Indexes: Hash indexes use a hash function to map keys to locations in memory. This approach facilitates lightning-fast lookups but struggles with range queries. Think of a library card catalogue sorted by unique call numbers, providing rapid access to specific books.
Bitmap Indexes: Bitmap indexes represent data as a bitmap, where each bit corresponds to a row in a table. They excel in scenarios with low cardinality data. Imagine a survey database; a bitmap index could efficiently indicate which respondents are from a specific city.
GiST (Generalised Search Tree) Indexes: GiST indexes are highly versatile and can handle complex data types such as geometric shapes or text. In geographic information systems (GIS), GiST indexes make spatial queries efficient by indexing geographical coordinates.

Creating and Managing Indexes

Creating Indexes

Creating an index is the initial step in the art of data organisation. It involves specifying three key components: the target table, the column(s) to be indexed, and the type of index to be employed. This process is akin to instructing the library staff to craft a comprehensive index for all the books based on the authors’ names.

Target Table: Think of the target table as the library’s entire collection. It’s the repository of all your data, from customer information to product details, much like a library that houses a diverse array of books.
Indexed Column(s): Just as a library catalogue might index books by author, title, and genre, you choose specific columns in your table to index. These columns are carefully selected based on your application’s query patterns. For example, in an e-commerce database, you might create an index on the ‘product name’ column to expedite searches for specific products.
Type of Index: The type of index you choose depends on your specific requirements. For example, if you prioritise efficient range queries on numerical values, a B-Tree index might be your tool of choice. This decision is akin to selecting the right indexing method for a library catalogue — whether it’s an alphabetical author index or a subject index.

Below given are a few basic examples of how to create indexes for SQL tables. Try executing them and then executing a few queries, observe the performance of your queries after adding indexes.

-- Creating an index on the 'product_name' column in an e-commerce database
CREATE INDEX idx_product_name ON products (product_name);

-- Creating a unique index on the 'email' column in a user table
CREATE UNIQUE INDEX idx_unique_email ON users (email);

Adding and Removing Indexes

In the dynamic realm of databases, data access patterns can evolve. New data arrives, and some information may become obsolete. In such cases, indexes can be added or removed to adapt to changing requirements.

Adding Indexes: Think of adding an index as updating the library catalogue when new books arrive. As your data grows, you may identify new columns that should be indexed to enhance query performance. Adding these indexes is like expanding the catalogue to include newly acquired books, ensuring that they can be quickly found by library visitors.
Removing Indexes: Similarly, just as a library may decide to remove entries for books that are no longer part of its collection, database administrators can choose to remove indexes that are no longer serving a valuable purpose. Over time, certain indexes may become redundant, or the query patterns they were designed for may change. Removing them streamlines database operations and reduces overhead.

Below given are a few basic examples of how to add and remove indexes from SQL tables.

-- Adding an index on the 'author' column in a books table
ALTER TABLE books ADD INDEX idx_author (author);

-- Removing an index on the 'obsolete_column' column
ALTER TABLE my_table DROP INDEX idx_obsolete_column;

Regular Index Maintenance

Imagine a library catalogue that hasn’t been updated or maintained for years. It would likely become inaccurate and inefficient. Similarly, regular index maintenance is vital for database performance.

Ensuring Optimal Performance: Regular maintenance ensures that your indexes remain efficient and responsive. It’s akin to the periodic upkeep of a library catalogue to keep it accurate and user-friendly.
Monitoring and Rebuilding: Database administrators monitor index usage and, if necessary, rebuild or reorganise indexes to eliminate fragmentation. Think of this as reordering a library’s bookshelves to keep related books together, making them easier to find.

Below given are a few basic examples of how to maintain indexes for SQL tables. Try executing them and observe the output you receive.

-- Check index fragmentation and analyze table
ANALYZE TABLE my_table;

-- Rebuild a specific index
ALTER TABLE my_table ENGINE=InnoDB;

-- Remove an index
DROP INDEX idx_to_remove ON my_table;

Indexing Best Practices

When to use indexes: Indexes should be employed judiciously. Too many indexes can slow down write operations and consume excessive storage space. Determine which columns benefit most from indexing based on query patterns.
Selecting the right columns for indexing: Choose columns for indexing based on their relevance to common query predicates. For example, in an e-commerce database, indexing product categories, prices, and customer IDs can significantly enhance search performance.
Monitoring and analysing index performance: Regularly monitor index usage and query performance. Tools and database management systems provide insights into which indexes are effective and which may need optimisation or removal.

Query Optimisation with Indexes

In the realm of database management, query optimisation is akin to a well-orchestrated symphony. The efficiency with which data is retrieved can make the difference between a sluggish, unresponsive application and one that delights users with swift interactions. Indexes, those clever data structures we place on columns within database tables, are like the conductors of this symphony, ensuring that queries play harmoniously and without delay.

Imagine a scenario where you’re the proprietor of an online store brimming with a diverse range of products. You receive a request from a customer to display all products within a specific price range, let’s say between ₹50 and ₹100. Without an index, the database engine would need to undertake a full table scan — examining every product in your vast inventory to filter out those within the desired price range. This process can be painstakingly slow, especially when dealing with large datasets, leaving your customer waiting for their results.

Now, introduce an index on the ‘price’ column of your product database. This index works like a magical map, listing prices and the corresponding product IDs, all neatly organised. When your customer initiates the query to find products between ₹50 and ₹100, the database engine consults this index first. Instead of laboriously scanning every product, it swiftly references the index, identifying the product IDs that fall within the desired price range.

But the magic doesn’t stop there. Armed with these product IDs, the database engine can now directly access the relevant rows in your product table, retrieving only the essential information about those products. This is the power of an index — it reduces the number of rows that need to be examined, transforming a potentially slow and resource-intensive operation into a lightning-fast index lookup.

Here’s a step-by-step execution of how this process unfolds in a SQL database:

Query Initialisation: The customer initiates a query to find products priced between ₹50 and ₹100.
Index Consultation: The database engine, armed with the index on the ‘price’ column, quickly identifies which rows in the index meet the price criteria. It’s like flipping through an index at the back of a book to find the pages related to a specific topic.
Retrieving Product IDs: The database engine retrieves the product IDs corresponding to the selected prices from the index. These product IDs serve as pointers to the actual rows in the product table.
Direct Row Access: With the product IDs in hand, the database engine directly accesses the relevant rows in the product table. It doesn’t need to scan the entire table; it precisely targets the rows needed. It’s like going directly to the bookshelves that house the books you’re interested in, without checking every other shelf.
Result Delivery: The database engine presents the customer with a list of products that fall within the specified price range, all achieved at lightning speed.

This process illustrates the transformative power of indexing. What could have been a sluggish experience is now a smooth and efficient interaction for your customer. Indexes, in this context, act as the guardians of speed and responsiveness in your database system, ensuring that queries play in harmony, delivering results swiftly, and enhancing the user’s experience. So, the next time you’re searching for a book in the library, think of indexes in your database, streamlining data retrieval and making it a truly delightful experience.

Common Indexing Pitfalls

Over-indexing: Excessive indexing can lead to increased write times, additional storage overhead, and potential performance bottlenecks. It’s like having too many indexes in a library catalogue, making it unwieldy and challenging to maintain.
Imagine a user table in a database with an index on a ‘gender’ column. If the ‘gender’ column only has a few unique values (e.g., ‘Male,’ ‘Female,’ ‘Non-Binary’), creating an index on this low-cardinality column could be unnecessary.
Lets take another example, consider a ‘updatedAt’ timestamp column in a table that gets updated frequently, but an index is created on this column. This could lead to significant write-time overhead, as the index needs to be updated with each change.
Under-indexing: Insufficient indexing can result in slow query performance, as the database must perform full-table scans. Consider a database with a ‘users’ table and an ‘orders’ table. If the ‘user_id’ in the ‘orders’ table, which is a foreign key referencing ‘users,’ is not indexed, querying orders for a specific user could be significantly slower.
Index fragmentation: Over time, indexes can become fragmented, leading to suboptimal performance. Think of it as a library catalogue that hasn’t been organised or updated in years; it becomes less efficient and inaccurate.

Conclusion

In the intricate world of database management, the optimisation of queries stands as a critical performance milestone. The role of indexes in this pursuit cannot be overstated. As we’ve discovered, these small but mighty data structures transform ponderous table scans into lightning-fast index lookups, ensuring that your database operations hum along harmoniously.

In essence, indexing is the conductor of a symphony of database efficiency. It orchestrates queries, ensuring they play in harmony, producing results with speed and precision. From e-commerce platforms to healthcare systems, from social media networks to financial institutions, the impact of well-implemented indexing reverberates across a myriad of applications, transforming the way we interact with data.

If you have any more questions or insights, or if you’d like to delve deeper into the fascinating world of database indexing or any related topics, I encourage you to reach out. Please feel free to contact me via email at hemang.dtu@gmail.com. I’m eager to engage in enriching discussions with fellow professionals like yourself.