SQL Indexes — Avoid 90-Minute Foreign Key Lock

Indexes are the single most impactful optimization available in relational databases. A query that takes 30 seconds on a table with 10 million rows can return in 2 milliseconds after adding the right index. The difference is the gap between scanning every row on disk and jumping directly to the matching ones via a balanced tree structure.

However, indexes aren't free. The cost is write overhead and disk space. Every index you add slows down INSERT, UPDATE, and DELETE operations because the database must maintain the sorted order of the index in real-time. The 'Forge' philosophy is about precision: adding the exact indexes your critical queries need without bloating the table with redundant metadata.

How a B-Tree Index Works

Most relational databases (PostgreSQL, MySQL, SQL Server) use a B-tree (Balanced Tree) structure for indexing. A B-tree keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic time.

When you query a column, the database starts at the root node and follows pointers down to the leaf nodes. Each leaf node contains the indexed value plus a 'Record Identifier' (RID) or a Primary Key value that points to the actual row data in the 'Heap' or Clustered Index.

-- io.thecodeforge.optimization
-- Scenario: Searching for a customer without an index
SELECT * FROM io_thecodeforge.orders WHERE customer_id = 1234;
-- Result: The database performs a 'Sequential Scan' (reads 100% of the table).

-- Optimization: Create a standard B-tree index
CREATE INDEX idx_orders_customer 
ON io_thecodeforge.orders(customer_id);

-- Now the query engine performs an 'Index Scan'
EXPLAIN ANALYZE 
SELECT * FROM io_thecodeforge.orders WHERE customer_id = 1234;

Composite Indexes and the Leftmost Prefix Rule

A composite index (multi-column index) is sorted primarily by the first column, then the second, and so on. This leads to the Leftmost Prefix Rule: an index on $(A, B)$ can be used for queries filtering by $(A)$ or $(A, B)$, but it is completely useless for a query filtering only by $(B)$.

Choosing the right column order is vital. Generally, you put the column with the highest cardinality (most unique values) first, or the column most frequently used in WHERE clauses.

-- io.thecodeforge.optimization
-- Composite index on (last_name, first_name)
CREATE INDEX idx_user_full_name 
ON io_thecodeforge.users(last_name, first_name);

-- VALID: Uses index (leftmost prefix)
SELECT * FROM io_thecodeforge.users WHERE last_name = 'Jordan';

-- VALID: Uses index (exact match)
SELECT * FROM io_thecodeforge.users WHERE last_name = 'Jordan' AND first_name = 'Michael';

-- INVALID: Full Table Scan (skips the first column of the index)
SELECT * FROM io_thecodeforge.users WHERE first_name = 'Michael';

-- PRO TIP: Covering Index
-- This index contains ALL data needed, avoiding a 'Table Lookup' entirely
CREATE INDEX idx_covering_search 
ON io_thecodeforge.users(email) INCLUDE (user_id, status);

When Indexes Hurt: Write Amplification and Bloat

Every time you INSERT a row into a table with 5 indexes, the database must perform 6 writes (1 to the table, 5 to the indexes). This is known as Write Amplification. Furthermore, indexes can become 'fragmented' over time as rows are deleted and updated, leading to bloated files and degraded performance.

-- io.thecodeforge.maintenance
-- 1. Identifying unused indexes (PostgreSQL example)
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND idx_read = 0;
-- Action: Drop these to reclaim write performance.

-- 2. Avoiding 'Functional' index traps
-- This ignores a standard index on 'created_at'
SELECT * FROM io_thecodeforge.orders WHERE DATE(created_at) = '2024-01-01';

-- Correct approach: Use a range or a functional index
CREATE INDEX idx_functional_date ON io_thecodeforge.orders ((DATE(created_at)));