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.

RT @MarkusWinand: €10 for a frontier AI subscription are gone in one workday €10 for my book SQL Performance Explained has a lasting impact on you* Learning timeless fundamentals always has a lasting effect * you also need to read it, which takes about 1 day ;)

RT @MarkusWinand: New post: Structured Primary Keys For consistency and performance.

14 years online, still relevant and current. Those who did the quiz 14 years ago can still benefit for their lessons at that time.

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: Covering many systems such as MySQL, PostgreSQL, Oracle, ...

Short-Circuiting Correlated Subqueries in SQLite

Another look into PostgreSQL CTE materialization and non-idempotent subqueries

RT @SQLPerfTips: 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)?

RT @MarkusWinand: My favorite change is that MariaDB got an improved "WHERE year(...) = ?" rewriting. 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

RT @MarkusWinand: Survey on performance optimization for database systems (2023)

RT @_backslashzero: #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 - #Databases #SQL

Improved RCSI Ghost Cleanup in SQL Server 2022 — by Paul White As always, Paul provides deep insight into the SQL Server implementation.

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:

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 Speaking in Tongues: PostgreSQL and Character Encodings

Farm to TABLE: Local(e) Providers The Doom That Came To PostgreSQL: When Collations Change

RT @MarkusWinand: 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 Best Practices Every Java Engineer Must Know [IMHO: not just Java Engineers]

RT @MarkusWinand: 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, ...): (but valid for all languages — see language selection at the top).