What every developer should know about SQL performance

Preface — Why is indexing a development task?

Anatomy of an Index — What does an index look like?

1. The Leaf Nodes — A doubly linked list

2. The B-Tree — It’s a balanced tree

3. Slow Indexes, Part I — Two ingredients make the index slow

The Where Clause — Indexing to improve search performance

1. The Equals Operator — Exact key lookup

   1. Primary Keys — Verifying index usage

   2. Concatenated Keys — Multi-column indexes

   3. Slow Indexes, Part II — The first ingredient, revisited

2. Functions — Using functions in the where clause

   1. Case-Insensitive Search — UPPER and LOWER

   2. User-Defined Functions — Limitations of function-based indexes

   3. Over-Indexing — Avoid redundancy

3. Bind Variables — For security and performance

4. Searching for Ranges — Beyond equality

   1. Greater, Less and BETWEEN — The column order revisited

   2. Indexing SQL LIKE Filters — LIKE is not for full-text search

   3. Index Combine — Why not using one index for every column?

5. Partial Indexes — Indexing selected rows

6. NULL in the Oracle Database — An important curiosity

   1. NULL in Indexes — Every index is a partial index

   2. NOT NULL Constraints — affect index usage

   3. Emulating Partial Indexes — using function-based indexing

7. Obfuscated Conditions — Common anti-patterns

   1. Dates — Pay special attention to DATE types

   2. Numeric Strings — Don’t mix types

   3. Combining Columns — use redundant where clauses

   4. Smart Logic — The smartest way to make SQL slow

   5. Math — Databases don’t solve equations

Testing and Scalability — About hardware

1. Data Volume — Sloppy indexing bites back

2. System Load — Production load affects response time

3. Response Time and Throughput — Horizontal scalability

The Join Operation — Not slow, if done right

1. Nested Loops — About the N+1 selects problem in ORM

2. Hash Join — Requires an entirely different indexing approach

3. Sort-Merge Join ‌— Like a zipper on two sorted sets

Clustering Data — To reduce IO

1. Index Filter Predicates Intentionally Used — to tune LIKE

2. Index-Only Scan — Avoiding table access

3. Index-Organized Table — Clustered indexes without tables

Sorting and Grouping — Pipelined order by: the third power

1. Indexed Order By — where clause interactions

2. ASC/DESC and NULL FIRST/LAST — changing index order

3. Indexed Group By — Pipelining group by

Partial Results — Paging efficiently

1. Selecting Top-N Rows — if you need the first few rows only

2. Fetching The Next Page — The offset and seek methods compared

3. Window-Functions — Pagination using analytic queries

Insert, Delete and Update — Indexing impacts on DML statements

1. Insert — cannot take direct benefit from indexes

2. Delete — uses indexes for the where clause

3. Update — does not affect all indexes of the table

Execution plans — getting and reading database execution plans

1. DB2 LUW : Getting • Operations • Access vs. filter predicates

2. MySQL : Getting • Operations • Access vs. filter predicates

3. Oracle : Getting • Operations • Access vs. filter predicates

4. PostgreSQL : Getting • Operations • Access vs. filter predicates

5. SQL Server : Getting • Operations • Access vs. filter predicates

6. SQLite : Getting • Operations

7. Gupta SQLBase : Getting • Operations

Myth Directory — Common myth and misbeliefs

1. Indexes Can Degenerate

2. Most Selective First

3. Oracle Cannot Index NULL

4. Dynamic SQL is Slow

5. Select * is Bad

Example Schema — CREATE and INSERT scripts

Glossary — Important terms briefly explained