How to Find Redundant Indexes in SQL

The following two indexes are redundant in most SQL databases: CREATE INDEX i_actor_1 ON actor (last_name); CREATE INDEX i_actor_2 ON actor (last_name, first_name); It is usually safe to drop the first index, because all queries that query the LAST_NAME column only can still profit from the second index I_ACTOR_2. The reason being that LAST_NAME is … Continue reading How to Find Redundant Indexes in SQL

Don’t Use the String Concatenation “Trick” in SQL Predicates

In SQL, quite often, we want to compare several values with each other. For instance, when we're looking for a specific user by their first and last names, we'll write a query like this one: SELECT * FROM customer WHERE first_name = 'SUSAN' AND last_name = 'WILSON'; We're getting: CUSTOMER_ID FIRST_NAME LAST_NAME ------------------------------------ 8 SUSAN … Continue reading Don’t Use the String Concatenation “Trick” in SQL Predicates

Faster SQL Through Occasionally Choosing Natural Keys Over Surrogate Keys

There are many many opinions out there regarding the old surrogate key vs. natural key debate. Most of the times, surrogate keys (e.g. sequence generated IDs) win because they're much easier to design: They're easy to keep consistent across a schema (e.g. every table has an ID column, and that's always the primary key) They're … Continue reading Faster SQL Through Occasionally Choosing Natural Keys Over Surrogate Keys

Many SQL Performance Problems Stem from “Unnecessary, Mandatory Work”

Probably the most impactful thing you could learn about when writing efficient SQL is indexing. A very close runner-up, however, is the fact that a lot of SQL clients demand tons of "unnecessary, mandatory work" from the database. Repeat this after me: Unnecessary, Mandatory Work What is "unnecessary, mandatory work"? It's two things (duh): Unnecessary … Continue reading Many SQL Performance Problems Stem from “Unnecessary, Mandatory Work”

jOOQ Tuesdays: Brett Wooldridge Shows What it Takes to Write the Fastest Java Connection Pool

Welcome to the jOOQ Tuesdays series. In this series, we’ll publish an article on the third Tuesday every other month where we interview someone we find exciting in our industry from a jOOQ perspective. This includes people who work with SQL, Java, Open Source, and a variety of other related topics. I'm very excited to … Continue reading jOOQ Tuesdays: Brett Wooldridge Shows What it Takes to Write the Fastest Java Connection Pool

Why You Should (Sometimes) Avoid Expressions in SQL Predicates

I've recently discovered a rather significant performance issue on a productive Oracle 11g customer database. And I'm sure you have this issue too, which is why I'm documenting it here. This is a simplified representation of the setup at the customer site: ID PAYMENT_DATE TEXT ---------- ------------ ----------------------------------- 33803 21.05.16 DcTNBOrkQIgMtbietUWOsSFNMIqGLlDw... 29505 09.03.16 VIuPaOAQqzCMlFBYPQtvqUSbWYPDndJD... 10738 … Continue reading Why You Should (Sometimes) Avoid Expressions in SQL Predicates

Don’t Even use COUNT(*) For Primary Key Existence Checks

In a recent blog post, I've advocated against the use of COUNT(*) in SQL, when a simple EXISTS() would suffice. This is important stuff. I keep tuning productive queries where a customer runs a COUNT(*) query like so: SELECT count(*) INTO v_any_wahlbergs FROM actor a JOIN film_actor fa USING (actor_id) WHERE a.last_name = 'WAHLBERG' ... … Continue reading Don’t Even use COUNT(*) For Primary Key Existence Checks

Does Your Database Really Use Your Index?

Adding the right index to speed up your queries is essential. But after a while, as your system grows, you may find yourself with tons of indexes, which all slow down writing to the database - as with each write to the table, the index needs to be updated as well in the same transaction. … Continue reading Does Your Database Really Use Your Index?

Avoid Using COUNT() in SQL When You Could Use EXISTS()

A while ago, I blogged about the importance of avoiding unnecessary COUNT(*) queries:https://blog.jooq.org/sql-tip-of-the-day-be-wary-of-select-count ... and how to replace them with equivalent EXISTS queries As I'm updating the SQL training to show also PostgreSQL performance characteristics in addition to Oracle, I really have to reiterate this topic. Please repeat after me: Thou shalt not use COUNT(*) … Continue reading Avoid Using COUNT() in SQL When You Could Use EXISTS()

jOOQ Tuesdays: Chris Saxon Explains the 3 Things Every Developer Should Know About SQL

Welcome to the jOOQ Tuesdays series. In this series, we’ll publish an article on the third Tuesday every other month (today, exceptionally on a Wednesday because of technical issues) where we interview someone we find exciting in our industry from a jOOQ perspective. This includes people who work with SQL, Java, Open Source, and a … Continue reading jOOQ Tuesdays: Chris Saxon Explains the 3 Things Every Developer Should Know About SQL