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

SQL Tips and Tricks

github.com

Post reply on HN
Print view View original

151–160 of 168 posts

Re: SQL Tips and Tricks

#151

Post by AtNightWeCode »

TheCycoONE wrote: post #121
AtNightWeCode wrote: post #101

A common mistake I see is that people think foreign keys will automatically create indexes. Missing indexes is a general problem in SQL. Missing indexes on columns that are in foreign keys are even worse.

In some RDBMS a foreign key will automatically create an index: https://dev.mysql.com/doc/refman/8.4/en/create-table-foreign... I think this falls under the read the documentation fully point. Edit: It occurs to me you likely meant on the column itself rather than on the referenced column. I don't have an example that does that.

Both columns needs the correct type of index. The best thing to do is to use a tool that scans for missing indexes.

Re: SQL Tips and Tricks

#152

Post by freilanzer »

egormakarov wrote: post #94
freilanzer wrote: post #89

Earlier quoted context omitted.

Php has nothing like this? In [1]: "... WHERE " + " AND ".join(str(i) for i in range(4)) Out[1]: '... WHERE 0 AND 1 AND 2 AND 3' Very strange.

This will produce broken SQL on empty clauses list. Very strange.

You're quite right, but this is easily fixed. That doesn't change my question, since something like this is much easier that the other logic.

Re: SQL Tips and Tricks

#153

Post by swarnie »

password4321 wrote: post #146
swarnie wrote: post #35

Earlier quoted context omitted.

Alternatively, write a mess of SQL like a three year old child that just discovered MSPaint then push the "beautifier" button and knock off for an early lunch.

Where am I supposed to park my bike if the shed is gone?! Closer to more seriously: which "beautifier" button is best? Is there a free one that is close to industry standard?

I can't really answer that i'm afraid, my chosen IDE (PL/SQL Dev) comes with one as standard.

Re: SQL Tips and Tricks

#154

Post by rldjbpin »

i noticed a lot of these advise used by senior devs in my team or in legacy code. but as someone just starting out, a lot of these (like "1=1") was very odd and made the queries less accessible.

nice to finally found the term for the "anti-query"; learning about it really changed how i write queries. equally good to see that most of these apply regardless of the RDBMS of choice.

Re: SQL Tips and Tricks

#155

Post by magicalhippo »

password4321 wrote: post #139
magicalhippo wrote: post #136

Earlier quoted context omitted.

Yes, as I noted. Frequently this is trivial, sometimes it's not. If there will be multiple hits but it doesn't matter that much, there's the obvious TOP 1 or MIN(col) and such. It's a tradeoff between accidentally breaking the query and returning unexpected data. Note that if you used join you could have bigger issues as the join would succeed but now you got multiple rows where you didn't expect.

Are there any tools or tips to help speed up the "which JOIN is duplicating data" hunt? Usually my biggest problem is getting all the query parameters lined up to reproduce the issue! (Being able to flip on extended logging or a profiler can make this easy.) Cutting out the result columns when disabling JOINs to narrow it down is straightforward but tracking columns down in WHERE clauses quickly tends not to be.

Good question. Obviously a profiler or similar that can capture the details when it happens helps, as you note.

If you can reproduce the issue then what I tend to do is to include the unique id column from each joined table (we try to avoid natural keys).

If it doesn't have a unique id column I replace the join with a subquery that includes row_number(), so I can se which one that doesn't repeat.

But without being able to replicate, I don't know of any better way than just studying the ON conditions carefully.

Re: SQL Tips and Tricks

#156

Post by jghn »

silveraxe93 wrote: post #13

The "readability" section has 3 examples. The first 2 are literally sacrificing readability so it's easier to write, and the last has an unreadable abomination that indenting is really not doing much.

> The first 2 are literally sacrificing readability

Please point me to the objective measurement of readability that you're using.

Appeals to "readability", for instance both what the OP and yourself are doing, are always 100% subjective.

Re: SQL Tips and Tricks

#157

Post by jghn »

arp242 wrote: post #99
silveraxe93 wrote: post #37

Earlier quoted context omitted.

Yeah, unfortunately you're right that they are real conventions. Quite common too. I also _understand_ why they exist. It's simple: It makes code marginally easier to write. But writing confusing, unintuitive and honestly plain ugly code. Just so you can save a second after clicking run and the compiler tells you the mistake is a bad reason.

A lot of "readability" depends on what you're used to and what you expect. I don't think these conventions are inherently "ugly" or "confusing", but they are different to what I've been doing for a long time, and thus unexpected, and thus "ugly". But that's extremely subjective. I've done plenty of SQL, and I've regularly run in to the "fuck about with fucking trailing commas until it's valid syntax"-problem. It's a…

> A lot of "readability" depends on what you're used to and what you expect.

Yes. Typically shared sense of "readability" in a community for language X translates to "idiomatic patterns when writing X". There's no real thing as readability in a universal sense. It's a placeholder statement for "it's easier for ME to understand", double emphasis on "ME". Within a community, "readability" standards are merely channeling the idiomatic patterns within that community as for most members they'll be easier for the person to understand as it's what they're used to seeing.

Re: SQL Tips and Tricks

#158

Post by NortySpock »

password4321 wrote: post #146
swarnie wrote: post #35

Earlier quoted context omitted.

Alternatively, write a mess of SQL like a three year old child that just discovered MSPaint then push the "beautifier" button and knock off for an early lunch.

Where am I supposed to park my bike if the shed is gone?! Closer to more seriously: which "beautifier" button is best? Is there a free one that is close to industry standard?

SQLFluff is a linter formatter written in Python https://sqlfluff.com/

Heard there is a faster one called sqruff, but I have not tried it

https://www.quary.dev/blog/sqruff-launch

Might also be able to cajole sqlglot into being a formatter but it's designed for forgiving dialect conversion, not formatting

https://github.com/tobymao/sqlglot

Re: SQL Tips and Tricks

#159

Post by paperplatter »

freilanzer wrote: post #152
egormakarov wrote: post #94

Earlier quoted context omitted.

This will produce broken SQL on empty clauses list. Very strange.

You're quite right, but this is easily fixed. That doesn't change my question, since something like this is much easier that the other logic.

The easiest fix for this is the "WHERE 1=1" or "WHERE true"

Re: SQL Tips and Tricks

#160

Post by AtNightWeCode »

the_gorilla wrote: post #116
AtNightWeCode wrote: post #107

Earlier quoted context omitted.

Read my other comments. I worked with SQL on and off since the last century. It has nothing to do with your poor assumptions.

Duration of working with SQL doesn't matter. The better SQL programmers don't do it specifically, and have experience in real languages that they bring over to database queries.

Not sure I get this. But I think it does matter since you understand why people do it to begin with. I worked on two enterprise solutions over the last couple of years that have this exact problem. That people are using WHERE 1=1 and then add random "AND something=something" that completely trashes the performance of the db. Also, it does not matter as much on-prem. But in cloud envs it does. Since you can't really spike CPU and mem the same way as on-prem.

The reason I pointed out this specific issue is just that I thought it was the worsed of many poor tips. ChatGPT can give better tips.

Post reply on HN