[go: up one dir, main page]
More Web Proxy on the site http://driver.im/

DE | FR | ES | JA ]

by Markus Winand.

SQL Indexing and Tuning e-Book


A site explaining SQL indexing to developers—no crap about administration.

SQL indexing is the most effective tuning method—yet it is often neglected during development. Use The Index, Luke explains SQL indexing from grounds up and doesn’t stop at ORM tools like Hibernate.

Use The Index, Luke is the free web-edition of my book SQL Performance Explained (from Є9.95). Be sure to subscribe my free newsletter.

SQL Indexing in MySQL, Oracle, SQL Server, etc.

Use The Index, Luke presents indexing in a vendor agnostic fashion. Product specific notes are provided like here:

Db2 (LUW)

Use The Index, Luke covers SQL indexing for IBM Db2. Tests were conducted with Db2 for Linux, UNIX and Windows, (LUW) V10.5 through 11.5.

MySQL

Use The Index, Luke covers SQL indexing for MySQL. Tests were conducted with MySQL 5.5 through 8.0.34.

Oracle

Use The Index, Luke covers SQL indexing for the Oracle database. Tests were conducted with Oracle 11g through 23ai.

PostgreSQL

Use The Index, Luke covers SQL indexing for PostgreSQL. Tests were conducted with PostgreSQL 9.0 through 16.

SQL Server

Use The Index, Luke covers SQL indexing for Microsoft SQL Server. Tests were conducted with SQL Server 2008R2 through 2022.

Have more questions about SQL indexing or tuning? No problem—have a look at my training and tuning services at winand.at.

Table of Contents

  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

      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 SearchUPPER 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 FiltersLIKE 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

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

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

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

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

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

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

You can’t learn everything in one day. Subscribe the newsletter via E-Mail, Bluesky or RSS to gradually catch up. Have a look at modern-⁠sql.com as well.

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at modern-sql.com. Previously he made use-the-index-luke.com, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via winand.at.

Buy the Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at Amazon.com.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »

Connect with Markus Winand

Subscribe mailinglistsSubscribe the RSS feedMarkus Winand on LinkedInMarkus Winand on XINGMarkus Winand on TwitterMarkus Winand on Bluesky
Copyright 2010-2024 Markus Winand. All righs reserved.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR