Everything developers need to know about SQL performance

Preface — Why developers need to index
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 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

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

Show some Love!

Buying the PDF supports our work on this site.
And you'll get a beautiful e-book.

Execution Plans — Fetching and reading execution plans
MySQL • Oracle • PostgreSQL • SQL Server
Myth Directory — Common myth and misbeliefs
Example Schema — CREATE and INSERT scripts
MySQL • Oracle • PostgreSQL • SQL Server

About the Author

As an author, trainer, and coach Markus Winand specializes in helping developers cope with SQL performance issues. He also published the book SQL Performance Explained and tweets his best performance tips via @SQLPerfTips.http://winand.at/

Stay connected:
RSS Feed Like on Facebook Follow me on Twitter Share at Google+

Everything developers need to know about SQL performance

About the Author

The 3-Minute Test

Table of Contents

Latest Blog Posts

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

For a Simple Select, At what point do indexes considerably speed up the query?

query regd the CBO decision

Index search using the concatenated index and SECOND column in WHERE clause.