What every developer should know about SQL performance


You can run many examples from the book online at SQL Fiddle (work in progress). The sections that already have online examples are marked with the SQL Fiddle logo: Try online at SQL Fiddle. Running these examples is as easy as a click.

  1. Preface — Why is indexing a development task?

  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 Try online at SQL Fiddle

      2. Concatenated Keys — Multi-column indexes Try online at SQL Fiddle

      3. Slow Indexes, Part II — The first ingredient, revisited Try online at SQL Fiddle

    2. Functions — Using functions in the where clause

      1. Case-Insensitive SearchUPPER and LOWER Try online at SQL Fiddle

      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 Try online at SQL Fiddle

      2. Indexing SQL LIKE FiltersLIKE is not for full-text search Try online at SQL Fiddle

      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 Try online at SQL Fiddle

      1. NULL in Indexes — Every index is a partial index Try online at SQL Fiddle

      2. NOT NULL Constraints — affect index usage Try online at SQL Fiddle

      3. Emulating Partial Indexes — using function-based indexing Try online at SQL Fiddle

    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

  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 Bywhere 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

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

About our book “SQL Performance Explained”
This book is definitively worth having in the company library.
” — Joe Celko
  1. Execution Plans — Fetching and reading execution plans

    MySQLOraclePostgreSQLSQL Server

  2. Myth Directory — Common myth and misbeliefs

  3. Example SchemaCREATE and INSERT scripts

    MySQLOraclePostgreSQLSQL Server

About the Author

Photo of Markus Winand
Markus Winand tunes developers for high SQL performance. He also published the book SQL Performance Explained and offers in-house training as well as remote coaching at http://winand.at/

?Recent questions at
Ask.Use-The-Index-Luke.com

0
votes
1
answer
128
views

PostgreSQL Scripts: Performance Testing and Scalability problem and question

Nov 12 at 14:53 Markus Winand ♦♦ 936
testing postgresql scalability
0
votes
1
answer
499
views

PostgreSQL Bitmap Heap Scan on index is very slow but Index Only Scan is fast

Oct 31 at 11:31 Markus Winand ♦♦ 936
index postgresql postgres sql
3
votes
2
answers
554
views

pagination with nulls

Oct 29 at 22:39 Rocky 46
pagination