Use the index, Luke! A guide to database performance
SQL Indexing and Tuning e-Book for developers: Use The Index, Luke covers Oracle, MySQL, PostgreSQL, SQL Server, ...
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.MySQLUse The Index, Luke covers SQL indexing for MySQL. Tests were conducted with MySQL 5.5 through 8.0.34.OracleUse The Index, Luke covers SQL indexing for the Oracle database. Tests were conducted with Oracle 11g through 23ai.PostgreSQLUse The Index, Luke covers SQL indexing for PostgreSQL. Tests were conducted with PostgreSQL 9.0 through 16.SQL ServerUse 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 ContentsPreface — Why is indexing a development task?Anatomy of an Index — What does an index look like?The Leaf Nodes — A doubly linked listThe B-Tree — It’s a balanced treeSlow Indexes, Part I — Two ingredients make the index slowThe Where Clause — Indexing to improve search performanceThe Equals Operator — Exact key lookupPrimary Keys — Verifying index usageConcatenated Keys — Multi-column indexesSlow Indexes, Part II — The first ingredient, revisitedFunctions — Using functions in the where clauseCase-Insensitive Search — UPPER and LOWERUser-Defined Functions — Limitations of function-based indexesOver-Indexing — Avoid redundancyBind Variables — For security and performanceSearching for Ranges — Beyond equalityGreater, Less and BETWEEN — The column order revisitedIndexing SQL LIKE Filters — LIKE is not for full-text searchIndex Combine — Why not using one index for every column?Partial Indexes — Indexing selected rowsNULL in the Oracle Database — An important curiosityNULL in Indexes — Every index is a partial indexNOT NULL Constraints — affect index usageEmulating Partial Indexes — using function-based indexingObfuscated Conditions — Common anti-patternsDates — Pay special attention to DATE typesNumeric Strings — Don’t mix typesCombining Columns — use redundant where clausesSmart Logic — The smartest way to make SQL slowMath — Databases don’t solve equationsTesting and Scalability — About hardwareData Volume — Sloppy indexing bites backSystem Load — Production load affects response timeResponse Time and Throughput — Horizontal scalabilityThe Join Operation — Not slow, if done rightNested Loops — About the N+1 selects problem in ORMHash Join — Requires an entirely different indexing approachSort-Merge Join ‌— Like a zipper on two sorted setsClustering Data — To reduce IOIndex Filter Predicates Intentionally Used — to tune LIKEIndex-Only Scan — Avoiding table accessIndex-Organized Table — Clustered indexes without tablesSorting and Grouping — Pipelined order by: the third powerIndexed Order By — where clause interactionsASC/DESC and NULL FIRST/LAST — changing index orderIndexed Group By — Pipelining group byPartial Results — Paging efficientlySelecting Top-N Rows — if you need the first few rows onlyFetching The Next Page — The offset and seek methods comparedWindow-Functions — Pagination using analytic queriesInsert, Delete and Update — Indexing impacts on DML statementsInsert — cannot take direct benefit from indexesDelete — uses indexes for the where clauseUpdate — does not affect all indexes of the tableYou 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 AuthorMarkus 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.