SQL Performance Tips

SQL Performance Explained — A Book For Developers It focuses on what developers need to know. Just 200 pages…yet covering all major databases. sql-performance-explained.com

14 years online, still relevant and current. Those who did the quiz 14 years ago can still benefit for their lessons at that time. use-the-index-luke.com/3-minute-quiz

A B-tree is not a binary tree. A B-tree does not go out of balance. A B-tree is self-balancing: it does *not* require expensive operations to keep the balance.

Completley free web-book about SQL indexing: use-the-index-luke.com Covering many systems such as MySQL, PostgreSQL, Oracle, ...

Short-Circuiting Correlated Subqueries in SQLite emschwartz.me/short-circuiti…

Another look into PostgreSQL CTE materialization and non-idempotent subqueries shayon.dev/post/2025/124/…

For this WHERE, what index to create? WHERE subsidiary_id = 42 AND last_name LIKE '%INA%' Is (subsidiary_id) enough or should it be (subsidiary_id, last_name)? use-the-index-luke.com/sql/clustering…

My favorite change is that MariaDB got an improved "WHERE year(...) = ?" rewriting. use-the-index-luke.com/blog/2024-12/d… In picture: the green check mark used to be yellow, with the "a" footnote: Unconditionally rewritten—even if only a matching function-based index exists

Survey on performance optimization for database systems (2023) researchgate.net/publication/36…

#DoYouKnow? If your index is getting too big in size, you can index only a subset of the values using Partial Indexes. Read more here - use-the-index-luke.com/sql/where-clau… #Databases #SQL

Improved RCSI Ghost Cleanup in SQL Server 2022 — by Paul White As always, Paul provides deep insight into the SQL Server implementation. sql.kiwi/2024/12/improv…

Farm to TABLE: Local(e) Providers thebuild.com/blog/2024/11/1… The Doom That Came To PostgreSQL: When Collations Change thebuild.com/blog/2024/11/1…

If you are not sure what collations are about, please read the entire series by Christophe Pettus: “Gentlemen, this is a 🏈”: Glyphs, Encodings, Collations, and Locales thebuild.com/blog/2024/10/2… Speaking in Tongues: PostgreSQL and Character Encodings thebuild.com/blog/2024/10/2…

Collations are a wonderful feature. They solve problems way beyond case-insensitive search: ignoring accents and "numeric" sorting ('10' > '2') are just two more examples. However, they do have a performance impact as shown by this micro-benchmark: thebuild.com/blog/2024/11/2…

At about this time of the day, I run my open-to-everyone trainings. The last training this year is "SQL Performance Explained", for which I have one spot left. It starts Dec 2nd and is delivered in seven 2-hour long in-person sessions each Mon/Wed/Fri. #sql-performance-ks" target="_blank" rel="nofollow noopener">winand.at/sql-training/o…

@softilium Well, then read the article to see that it does make a big difference. use-the-index-luke.com/sql/clustering…

@SQLPerfTips I think "LIKE" doesn't use indexes. Because of this index for subsidiary_id is enough.

@davidrowley_pg Check again :

@SQLPerfTips It would be great to see this updated PostgreSQL 17 now that it's out.

Finding for the most recent, best matching, highest ranking rows (“Top-N”) is a common problem. The window functions ROW_NUMBER, RANK and DENSE_RANK can be used for such queries. Not all systems optimize these queries well—PostgreSQL 15 just learned it. use-the-index-luke.com/sql/partial-re…

SQL Best Practices Every Java Engineer Must Know foojay.io/today/sql-best… [IMHO: not just Java Engineers]

In the meanwhile enjoy a discount for SQL Performance Explained if you don't have it yet: 15YrsAnniversary Only valid when purchasing directly from me (not on Amazon, ...): sql-performance-explained.com (but valid for all languages — see language selection at the top).