Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL Anti-Patterns

datamethods.substack.com

Post reply on HN
Print view View original

151–160 of 222 posts

Re: SQL Anti-Patterns

#151

Post by jdnier »

FuriouslyAdrift wrote: post #143

The single biggest thing that helped me speed up my queries and lower resource usage on the server was focusing on making my queries more sargable. https://en.wikipedia.org/wiki/Sargable https://www.brentozar.com/blitzcache/non-sargable-predicates...

Looking up the etymology of "sargeable", I found this StackOverflow answer: https://dba.stackexchange.com/a/217983

And Google explains "The term 'sargable' is a portmanteau of "Search ARGument ABLE," formed by combining the words from a SQL database context."

Re: SQL Anti-Patterns

#152

Post by bdangubic »

sgarland wrote: post #144
sigwinch28 wrote: post #38

Earlier quoted context omitted.

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?)

Because a city/region/state can be uniquely identified with a postal code (hell, in Ireland, the entire address is encapsulated in the postal code), but the reverse is not true. At scale, repeated low-cardinality columns matter a great deal.

saying zipcodes uniquely identify city/state/region is like saying John uniquely identifies a human :)

Re: SQL Anti-Patterns

#153

Post by dotancohen »

sgarland wrote: post #150
dotancohen wrote: post #63

Earlier quoted context omitted.

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.

Right, that's why I add it.

Re: SQL Anti-Patterns

#154

Post by physicles »

ddxv wrote: post #124

I've built myself a few problems that I haven't fixed yet: Many materialized views that rely on materialized views. When one at the bottom, or a table, needs a changed all views need to be dropped and recreated. Using a warm standby for production. I love having a read only production database, but since it's not the primary, it always feels like it's on the losing end of the system. Recently upgraded to Postgres 18…

Why do you need a warm standby for production? Do you need >= 3 nines?

Our staging environment has its own instance that is rebuilt from prod, with pii removed, every day outside working hours (this normally takes about 15 minutes). It’s fantastic for testing migrations, and is easy to support compared with a warm standby.

Re: SQL Anti-Patterns

#155

Post by physicles »

skybrian wrote: post #28

> three or four layers of subqueries, each one filtering or aggregating the results of the previous one, totaling over 5000 lines of code In a better language, this would be a pipeline. Pipelines are conceptually simple but annoying to debug, compared to putting intermediate results in a variable or file. Are there any debuggers that let you look at intermediate results of pipelines without modifying the code?

I wrote some tooling to help debug sql queries with many CTEs. It parses the sql, finds all the CTEs, and prints the result of each CTE formatted as csv. If the .sql file changes on disk, it reruns the query and tells you which CTEs’ output changed. Saved me hours in debugging.

Re: SQL Anti-Patterns

#156

Post by mjb »

0xbadcafebee wrote: post #40

At this point it's malpractice not to use AI to analyze your SQL statements and tables for optimizations

I agree. Modern code models tend to do a great job advising in SQL, especially if you include the table definition and EXPLAIN output in the context. Alternatively, I've found that an EXPLAIN MCP tool works well.

Re: SQL Anti-Patterns

#157

Post by spprashant »

Not all of these are "anti-patterns", your query clause not matching your index is a problem of not understanding how indexes work.

Some of these have nothing to do with SQL the language itself, and more to do with database schema design. If you have to do a DISTINCT, it means your primary key design is likely not right. If you are layering too many views, something is broken in the base table design, requiring the creation of all these views.

A good database model goes a long way to avoiding all this.

Re: SQL Anti-Patterns

#158

Post by naasking »

crazygringo wrote: post #141
MaxBarraclough wrote: post #129

Earlier quoted context omitted.

> This is literally what NULL is for. It's a special value precisely because missing data or a N/A field is so common. Kinda. You need null for outer joins, but you could have a relational DBMS that prohibits nullable columns in tables. Christopher Date thought that in properly normalised designs, tables should never use nullable columns. Codd disagreed. [0] > If you're suggesting mandatory additional has_address and…

> The way to do it without using a nullable column I mean, you could, but having separate tables for every optional field would be an organizational and usability nightmare. Queries would be longer and slower for no good reason. Not to mention a gigantic waste of space with all those repeated primary keys and their indexes. And you could have databases that prohibited NULL values, but we mostly don't, because they're…

> but having separate tables for every optional field would be an organizational and usability nightmare

I think this indicates that declaring and managing state is too onerous in SQL.

Re: SQL Anti-Patterns

#159

Post by fipar »

EvanAnderson wrote: post #14

> 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.

I'm not sure I understand the part about set theory. If anything, a valid use of DISTINCT is if you want the result to be (closer to) a set, as otherwise (to your point, depending on the data model) you may get a bag instead.

In fact, IIRC, using DISTINCT (usually bad for performance, btw) is an SQL advice by CJ Date in https://www.oreilly.com/library/view/sql-and-relational/9781...

Re: SQL Anti-Patterns

#160

Post by fipar »

dotancohen wrote: post #63
EvanAnderson wrote: post #14

> 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.

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.

In mysql, the db will continue reading even if the limit condition has been met, and then anything beyond the limit will be discarded before returning the result.
Post reply on HN