sql / expert
Snippet
Composite Index Selectivity and Prefixing
When designing composite indexes, the 'Left-Prefix' rule dictates that the index can only be used if the leading column is present in the WHERE clause. Placing the most selective (high cardinality) column first maximizes the index's ability to filter out irrelevant rows early.
snippet.sql
1
2
3
CREATE INDEX idx_user_lookupON Users (tenant_id, status, last_login);-- Optimization: High cardinality leading columns
Breakdown
1
ON Users (tenant_id, status, last_login)
Defines an index spanning three columns in a specific order.
2
Optimization: High cardinality leading columns
A design principle suggesting that columns that uniquely identify more rows should usually come first.