Query optimization aims to reduce resource use and response time without changing the result's meaning. There is no universally fastest rewrite: data size, distributions, indexes, database statistics, concurrency, and the optimizer all influence execution. Measure a representative workload before and after changes.
Key Takeaways at a Glance:
- •B-Tree indexes speed up equality seeks and range scans by ordering keys in balanced tree data structures.
- •Composite Index Leftmost Prefix Rule: An index on (A, B, C) can satisfy queries filtering on (A), (A, B), or (A, B, C), but CANNOT satisfy queries filtering on (B) or (C) alone.
- •Covering Index: When all columns requested in the SELECT, WHERE, and ORDER BY clauses exist within the index itself, avoiding table page lookups entirely (Index Only Scan).
- •High selectivity columns (attributes with high uniqueness like user_id) benefit most from indexing; low selectivity columns (like gender or boolean flags) often cause the optimizer to choose a full table scan.
In Simple Words (The Mental Model)
An index is literally the index at the back of a textbook. If you want to find every mention of 'Transactions' in a 1,000-page book, you don't read all 1,000 pages (Table Scan). You flip to the index at the back (Index Seek), see 'pages 45, 112, 380', and jump directly to those exact pages.
Real-World Analogy
Think of an indexed library catalog. Books are arranged alphabetically by Author and Title. If you look for 'Rowling, J.K.', you walk straight to the 'R' shelf in 10 seconds. But if you ask the librarian: 'Find all books that have exactly 350 pages', the author index is useless—the librarian has to pick up every single book and count pages (Full Scan).