Everything developers need to know about SQL performance

  1. Preface — Why developers need to index

  2. 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

  3. 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

      4. Exercise — How to index by age?

    3. Bind Variables — For security and performance

    4. NULL and Indexes — About Oracle’s NULL oddity

      1. Indexing NULL — In the Oracle database

      2. NOT NULL Constraints — How it affects index usage

      3. Partial Indexes, Part I — Oracle’s way to partial indexes

    5. Searching for Ranges — Beyond equality

      1. Greater, Less and Between — The column order matters even more

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

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

    6. Obfuscated Conditions — Common anti-patterns

      1. Dates — Pay special attention to DATE types

      2. Numeric Strings — Don’t mix types

      3. Date/Time Concatenation — Using redundant where clauses

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

      5. Math — Databases don’t solve equations

  1. 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

  2. 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

  3. 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

  4. 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

  5. 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

  1. Execution Plans — Fetching and reading execution plans

    MySQL • Oracle • PostgreSQL • SQL Server

  2. Myth Directory — Common myth and misbeliefs

    1. Indexes Can Degenerate

    2. Most Selective First

    3. Oracle Cannot Index NULL

    4. Dynamic SQL is Slow

  3. Example Schema — CREATE and INSERT scripts

    MySQL • Oracle • PostgreSQL • SQL Server

Recent Questions at Ask.Use-The-Index-Luke.com

0
votes
1
answer
277
views

Updating multiple rows using a subquery in SQL

Jan 08 at 09:52 Jan 26
subquery update sql
1
vote
1
answer
210
views

Should 'id' (the primary key) be included in an index

Jan 03 at 15:24 Jan 26
index include
0
votes
1
answer
227
views

Best index for a multiple join-tables and filter

Jan 03 at 14:31 Markus Winand ♦♦ 216
index join where