Set your Publisher ID in src/config/ads.ts to display live ads.
Indexes, B-Trees & Query Optimization
Clustered vs non-clustered indexes, EXPLAIN plans, and sargable queries
Without indexes, databases must scan every row on disk (Full Table Scan). B-Tree indexes provide logarithmic O(log n) lookups, transforming multi-second queries into sub-millisecond responses.
Index Mechanics & Sargability
Clustered Index: Dictates the physical sort order of table rows on disk (usually the Primary Key). Exactly one per table.
Non-Clustered (Secondary) Index: A separate B-Tree data structure containing index keys and row pointers back to the base table.
Sargable Queries (Search Argument Able): Writing WHERE YEAR(OrderDate) = 2024 disables indexes because the function wraps the column! Write WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01' instead to utilize indexes.
EXPLAIN ANALYZE: Analyzes actual query execution paths, cache hits, and estimated vs real row counts.
-- Create composite B-Tree index on (Status, CreatedAt)
CREATE INDEX idx_orders_status_date
ON Orders (Status, CreatedAt DESC);
-- Inspect query execution plan
EXPLAIN ANALYZE
SELECT OrderID, TotalAmount
FROM Orders
WHERE Status = 'Shipped'
AND CreatedAt >= '2024-01-01';Set your Publisher ID in src/config/ads.ts to display live ads.
Test Yourself With Exercise
Why does writing `WHERE LOWER(email) = 'user@example.com'` frequently cause slow queries even if an index exists on `email`?