Comment by EvanAnderson 11 hours ago
> Overusing DISTINCT to “Fix” Duplicates
Any time I see DISTINCT in a query I immediately become suspicious that the query author has an incomplete understanding of the data model, a lack of comprehension of set theory, or more likely both.
That’s almost always my experience too.
Though fairly recently I learned that even with all the correct joins in place, sometimes adding a DISTINCT within a CTE can dramatically increase performance. I assume there’s some optimizations the query planner can make when it’s been guaranteed record uniqueness.
I've been told similar nasty things for adding LIMIT 1 to queries that I expect to return at most a single result, such as querying for an ID. But on large tables (at least in sqlite, mysql, and maybe postgress too) the database will continue to search the entire table after the given record was found.
If you include an ORDER BY, the DB _may_ continue searching. MySQL (and, I assume, MS SQL Server, since it also can cluster the PK) can stop early in some circumstances.
But if you just have a LIMIT, then no - any RDBMS should stop as soon as it’s reached your requested limit.
I've noticed that LIMIT 1 makes a huge difference when working with LATERAL JOINs in Postgres, even when the WHERE condition has a unique constraint.
Only if your table is missing an unique index on that column, which it should have to enforce your assumption, so yeah LIMIT 1 is a code (or schema in the case) smell.
IDs are typically unique primary key. But in my experience, adding LIMIT 1 would on average halve the time taken to retrieve the record.
I'll test again, really the last time I tested that was two decades ago.
That seems like your RDBMS wasn't handling something right there or there wasn't a unique index on the column.
Do you recall what the database server was?
Yes, I was using Mysql exclusively at the time. I don't recall which version.
I also tested this once years later when doing a Python app with sqlite. Similar result, but admittedly that was not a very big table to begin with.
I am meticulous with my database schemas, and periodically review my indexes and covering indexes. I'm no DBA, but I believe that the database is the only real value a codebase has, other than maybe a novel method here and there. So I put care into designing it properly and testing my assumptions.
You are certainly doing something wrong if that's true.
I'm curious, can you demo this?
I'm curious as well to see if this still holds up. I'll try this week.
It's the exact opposite in Cypher. I'm currently working with some complex data in neo4j, and wondered why my perfectly fine looking queries were so slow, until I remembered to use DISTINCT. It's very easy to get duplicate nodes in your results, especially when you use variable length relationships, and DISTINCT is the only fix I'm aware of that fixes that.
IDK, "which ZIP codes do we have customers in?" seems like a reasonable thing to want to know
The very next ask will be "order the zipcodes by number of customers" at which point you'll be back to aggregations, which is where you should have started
Anti-Patterns You Should Avoid: overengineering for potential future requirements. Are there real-life cases where you should design with the future in mind? Yes. Are there real-life cases where DISTINCT is the best choice by whatever metric you prioritize at the time? Also yes.
> Are there real-life cases where DISTINCT is the best choice by whatever metric you prioritize at the time
Indeed, along that line, I would say that DISTINCT can be used to convey intent... and doing that in code is important.
- I want to know the zipcodes we have customers in - DISTINCT
- I want to know how many customers we have in each zipcode - aggregates
Can you do the first with the second? Sure.. but the first makes it clear what your goal is.
Here we start to get close to analytics sql vs application sql, and I think that's a whole separate beast itself with different patterns and anti-patterns.
I think you're reading more into what was said than is really there
> I immediately become suspicious
All I read from that is, when DISTINCT is used, it's worth taking a look to make sure the person in question understands the data/query; and isn't just "fixing" a broken query with it. That doesn't mean it's wrong, but it's a "smell", a "flag" saying pay attention.
In my experience, its nearly as often a problem with the design of the database as the query author.
Set theory...
There are self-identifying "senior software engineers" that cannot understand what even an XOR is, even after you draw out the entire truth table, all four rows.
I am surprised at common it is for software engineers to not treat booleans properly. I can’t tell you how many times if seen ‘if(IsFoo(X) != false)’
It never used to bug me as a junior dev, but once a peer pointed this out it became impossible for me to ignore.
The most egregious one I saw, I was tracking down a bug and found code like this:
bool x;
...
if (x == true) {
DoThing1();
} else if (x == false) {
DoThing2();
}
Sometimes this kind of thing happens after a few revisions of code, where in earlier versions the structure of the code made more sense: maybe several conditions which were tested and then, due to changing requirements, they coalesced into something which now reads as nonsense.
When making a code change which touches a lot of places, it's not always obvious to "zoom out" and read the surrounding context to see if the structure of the code can be updated. The developer may be chewing through a grep list of a few dozen locations that need to be changed.
Clearly the correct spelling is
`if(X&IsFooMask != 0)`
:)
This is like saying the non-negative integers under addition, lists under append, and strings under concatenation are all just misnamings of the semigroup operator.
https://hackage.haskell.org/package/base-4.21.0.0/docs/Data-...
PostgreSQL's `DISTINCT ON` extension is useful for navigating bitemporal data in which I want, for example, the latest recorded version of an entry, for each day of the year.
There are few other legitimate use cases of the regular `DISTINCT` that I have seen, other than the typical one-off `SELECT DISTINCT(foo) FROM bar`.
Eh, sometimes you need a quick fix and it’s just extremely concise and readable. I’ll take an INNER JOIN over EXISTS (nice but insanely verbose) or CROSS APPLY (nice but slow) almost every time. Obviously you have to know what you’re dealing with, and I’m mostly talking about reporting, not perf critical application code.
Distinct is also easily explained to users, who are probably familiar with Excel’s “remove duplicate rows”.
It can also be great for exploring unfamiliar databases. I ask applicants to find stuff in a database they would never see by scrolling, and you’d be surprised how many don’t find it.
What do you mean? Here are your real alternatives for doing a semijoin (assuming ANSI SQL, no vendor extensions):
SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2 WHERE t2.x = t1.x );
SELECT * FROM t1 WHERE x IN ( SELECT x FROM t2 );
SELECT * FROM t1 JOIN ( SELECT DISTINCT x FROM t2 ) s1 USING (x);
You could argue that you could fake a semijoin using
SELECT DISTINCT * FROM t1 JOIN t2 USING (x);
SELECT * FROM t1 JOIN t2 USING (x) GROUP BY t1.*;
And that's okay. Not every developer knows every single thing there is to know about every single tech. Sometimes you just need a solution, and someone with more specific knowledge can optimize later. How many non-database related mistakes would you make if you had to build every part of a system yourself?
But what if they don't know that they need your approval not to know things?
Or it’s simply an indicator of a schema that has not been excessively normalised (why create an addresses_cities table just to ensure no duplicate cities are ever written to the addresses table?)