Database Indexing
Database indexing is the technique of creating auxiliary data structures — most commonly B-trees or hash tables — that let a database engine locate rows matching a query condition without scanning every row in a table.
22 resources across 2 libraries
Glossary Terms(3)
Database Normalization
Database normalization is the process of organizing tables and columns in a relational database to reduce data redundancy and prevent update, insert, and delet…
Database Indexing
Database indexing is the technique of creating auxiliary data structures — most commonly B-trees or hash tables — that let a database engine locate rows matchi…
Query Optimization
Query optimization is the process of improving how a database query is written and executed so it returns correct results using the least amount of time, memor…
Interview Questions(19)
Clustered vs Non-Clustered Index: What is the Difference?
A clustered index determines the physical order in which table rows are stored on disk, while a non-clustered index is a separate structure that stores pointer…
How Does a B-Tree Index Work Internally?
A B-tree index is a balanced, sorted tree of fixed-size nodes where every leaf sits at the same depth, letting the database find any key in O(log n) disk reads…
Hash Index vs B-Tree Index: What is the Difference?
A hash index maps each key to a bucket via a hash function, giving O(1) average-case exact-match lookups but no support for range queries or ordering, while a…
What is a Covering Index in SQL?
A covering index is an index that includes every column a query needs — both the filter/sort columns and the selected columns — so the database engine can answ…
What is a Partial Index and When Should You Use One?
A partial index is an index built over only the subset of rows that satisfy a WHERE condition, rather than every row in the table, which keeps it smaller, fast…
What Are Index Selectivity and Cardinality?
Cardinality is the number of distinct values in a column, and selectivity is the ratio of distinct values to total rows — a high-selectivity column (close to 1…
Index Scan vs Sequential Scan: When Does the Optimizer Choose Each?
A sequential scan reads every row of a table in physical order to find matches, while an index scan uses an index structure to jump directly to only the rows t…
How Does the Nested Loop Join Algorithm Work?
A nested loop join works by iterating over every row of an outer table and, for each one, scanning or probing an inner table to find matching rows, making it t…
How Does Full-Text Search Work in Relational Databases?
Full-text search in a relational database uses a dedicated inverted-index structure that maps normalized words to the rows containing them, letting the engine…
Elasticsearch vs Built-In Database Full-Text Search: When to Use Which?
Use your relational database’s built-in full-text search when search is a secondary feature on data that already lives there and needs to stay transactionally…
How Does Geospatial Indexing Work for GIS Queries?
Geospatial indexing organizes location data using space-partitioning structures like R-trees or grid/quadtree schemes so the database can answer "what is near…
How Do Trigram Indexes Enable Fuzzy Search in SQL?
A trigram index breaks every string into overlapping three-character sequences and indexes those fragments, letting the database find approximate or misspelled…
Why and How Should You Schedule Index Maintenance?
Index maintenance should be scheduled because indexes accumulate bloat and stale statistics from ongoing writes, which gradually degrades query performance unt…
What is a Bitmap Index and When Should You Use One?
A bitmap index stores one bit array per distinct column value, where each bit position corresponds to a row and is set to 1 if that row holds that value, letti…
What is a B-Tree?
A B-tree is a self-balancing, multi-way search tree where each node can hold multiple keys and multiple children, keeping the tree shallow so search, insert, a…
What is a B+ Tree and Why Do Databases Use It?
A B+ tree is a self-balancing, multi-way search tree where all actual data (or record pointers) lives only in the leaf nodes, internal nodes hold only routing…
What is Database Indexing at Scale?
Database indexing at scale is the practice of building auxiliary data structures, typically B-trees or hash indexes, that let queries locate rows without scann…
How Do You Choose an Indexing Strategy at Scale?
Choosing an indexing strategy at scale means matching the index structure to the query pattern — B-tree indexes for range scans and ordered access, hash indexe…
How Does Geospatial Indexing Work for Location-Based Queries?
Geospatial indexing makes “find nearby points” queries fast by transforming two-dimensional coordinates into a structure that groups spatially close points tog…