Everything developers need to know about SQL performance
Preface — Why developers need to index
Anatomy of an Index — What does an index look like?
The Leaf Nodes — A doubly linked list
The B-Tree — It’s a balanced tree
Slow Indexes, Part I — Two ingredients make the index slow
The Where Clause — Indexing to improve search performance
The Equals Operator — Exact key lookup
Primary Keys — Verifying index usage
Concatenated Keys — Multi-column indexes
Slow Indexes, Part II — The first ingredient, revisited
Functions — Using functions in the where clause
Case-Insensitive Search —
UPPERandLOWERUser Defined Functions — Limitations of function based indexes
Over Indexing — Avoid redundancy
Exercise — How to index by age?
Bind Variables — For security and performance
NULL and Indexes — About Oracle’s
NULLoddityIndexing NULL — In the Oracle database
NOT NULL Constraints — How it affects index usage
Partial Indexes, Part I — Oracle’s way to partial indexes
Searching for Ranges — Beyond equality
Greater, Less and Between — The column order matters even more
Indexing SQL LIKE Filters —
LIKEis not for full-text searchIndex Combine — Why not using one index for every column?
Obfuscated Conditions — Common anti-patterns
Dates — Pay special attention to
DATEtypesNumeric Strings — Don’t mix types
Date/Time Concatenation — Using redundant where clauses
Smart Logic — The smartest way to make SQL slow
Math — Databases don’t solve equations
Testing and Scalability — About hardware
Data Volume — Sloppy indexing bites back
System Load — Production load affects response time
Response Time and Throughput — Horizontal scalability
The Join Operation — Not slow, if done right
Nested Loops — About the N+1 selects problem in ORM
Hash Join — Requires an entirely different indexing approach
Sort-Merge Join — Like a zipper on two sorted sets
Clustering Data — To reduce IO
Index Filter Predicates Intentionally Used — to tune
LIKEIndex-Only Scan — Avoiding table access
Index-Organized Table — Clustered indexes without tables
Sorting and Grouping — Pipelined order by: the third power
Indexed Order By — where clause interactions
ASC/DESCandNULL FIRST/LAST— changing index orderIndexed Group By — Pipelining group by
Partial Results — Paging efficiently
Selecting Top-N Rows — if you need the first few rows only
Fetching The Next Page — The offset and seek methods compared
Window-Functions — Pagination using analytic queries
Execution Plans — Fetching and reading execution plans
MySQL • Oracle • PostgreSQL • SQL Server
Myth Directory — Common myth and misbeliefs
Example Schema —
CREATEandINSERTscriptsMySQL • Oracle • PostgreSQL • SQL Server
Stay connected:
RSS Feed
Like on Facebook
Follow me on Twitter
Share at Google+
share and subscribe
RSS FeedFlattr this! Follow me on TwitterShare at Google+Like on Facebook