Mastering the Modern Database: Indexes in Partitioned and Sharded Databases

This article discusses the concept of indexing in partitioned and sharded databases

1. Introduction to Database Indexing

Database indexing is a data structure technique used to improve the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. They are analogous to the index found at the end of a book, which provides quick and direct access to the information contained within the book.

There are several types of indexes in databases, each optimized for specific types of queries:

— B-tree indexes: The most common type of index, B-tree (Balanced Tree) indexes are structured in a way that allows searches, insertions, deletions, and sequential access in logarithmic time.
— Hash indexes: These are based on hash tables and are best for equality searches that use the equals operator.
— Composite indexes: These indexes involve multiple columns and are useful for queries that test all the columns in the index or a leftmost prefix of them.
— Full-text indexes: Designed for searching text strings, these indexes can be used to find words or phrases within text columns.

How Indexes Work
When a query is executed, the database engine first looks to see if there are any indexes that it can use to speed up the…