In this lesson, you will learn how the presence of NULL values influences the physical storage size and the search efficiency of your B-tree indexes.
The B-Tree and the NULL Challenge
When we create an index on a column that allows NULL values, the database engine must decide how to represent "missing" information within the B-tree structure. Because a NULL is not a value in the traditional sense—it represents an unknown—it doesn't have a natural place in a sorted sequence (like 1, 2, 3...).
To maintain the index, the engine must assign NULL a position, typically treating it as either the "lowest" or "highest" possible value. This adds a specific overhead to your index nodes in two primary ways:
1. Storage Overhead and Node Density
Every entry in a B-tree leaf node carries a small amount of metadata. When NULL values are indexed, they occupy physical space in the index leaf pages. Even though a NULL has no "data" content, the entry still stores the key reference and the pointer (RID or Clustering Key) to the actual table row.
If your table has millions of rows with NULL in the indexed column, those entries consume space, reducing the overall branching factor of your B-tree. When you have fewer entries per page, your index tree grows taller, which increases the index depth and the number of I/O operations required to traverse the tree.
2. Traversal Impact
During a search, the B-tree traversal logic must account for these NULL entries. If a query includes a WHERE column IS NULL clause, the engine can navigate directly to the dedicated section for NULLs. However, if your queries are almost exclusively searching for actual values, those NULL entries act as "dead weight." They increase the size of the index pages without helping your common search patterns, forcing the engine to scan through or skip over them during traversals.
-- An index on this column will include NULLs,
-- bloating the index size if many rows are empty.
CREATE INDEX idx_user_email ON users(email);
If you frequently filter by non-null values, the space occupied by NULLs effectively lowers your index's selectivity and caching efficiency, as fewer useful entries fit into the database buffer pool at one time.
Key Takeaway: Indexing NULL values increases the physical size of the B-tree and can degrade search performance by decreasing node density and potentially increasing the total depth of the index.