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

    3. Bind Variables — For security and performance

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

    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

  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

  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

  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
229
views

query regd the CBO decision

Apr 17 at 10:27 Hulda(suspended)
index-choice optimizer
0
votes
3
answers
2.0k
views

Examples for Function Based Indexes?

Mar 25 at 15:52 Castorp 1
function-based
0
votes
1
answer
610
views

Updating multiple rows using a subquery in SQL

Jan 08 at 09:52 Jan 26
subquery update sql