Both comments can work together. Yes, doing more work than necessary is bad (#1). The amount of "bad" is very low to barely measurable in most real-life scenarios (#2).

"The amount of "bad" is very low to barely measurable in most real-life scenarios (#2)."

When you constantly apply this "only things bigger than X matter" reasoning you still end up with slow, shitty software but you just died by a thousand cuts instead of 1.

And the rub is if you fail to apply it liberally enough you end up with no completed software or difficult to maintain software because someone went around applying every micro-optimisation in the book regardless of how nessasary or appropriate it might be.

"someone went around applying every micro-optimisation in the book regardless of how nessasary or appropriate it might be"

This feels like a strawman. I have yet to see an instance of this in my 10 years. The much, much, much more common case is people justifying half-assed shoddy work with YAGNI.

Survivorship bias suggests we're more likely to see projects that resulted in released software...

See: solo indie gamedevs that build their own engines for years and subsequently fail to release anything.

Fortunately, stars in sql are also really bad for maintenance ;-) So I guess there is indeed no reason to use them!

I work that way, and I don't end up slow, shitty software.

I do end up shipping working software much sooner than if I optimized every statement everywhere.

The correct answer to almost every db question is: “it depends”.

For instance. “Select *” can be good for ad-hoc exploration (as long as you include a limit to the rows returned). It’s bad practice for shipped code because it can lead to binding issues, in code, when you modify or add columns.

“Select *” will almost certainly be optimized away by the execution plan but if you have blob data, could introduce a lot of unnecessary disk i/o.

Is “select *” bad? It depends.

> “Select ” will almost certainly be optimized away by the execution plan

What do you mean? Ask for all the columns, you're gonna get all the columns in a resultset. Only in a subquery could the planner know what is unused.

'select ' is _perhaps_ tolerable for creating a view--under the conditions that the database transforms the query into the unambiguous named columns at view creation time--and that's about it.

You’re right. When I wrote that, I was thinking higher up the chain in an orm layer. Sending “select *” to the db can’t be “optimized away” at that layer, it will have to return all the columns. Unless, like you said, the scope of the “select *” contains it in a sub query where the columns used can be known... “it depends :)”

>> binding issues, in code, when you modify or add columns.

This is very common IME, and it is a very bad code smell. It tends to break things very low in the architecture, so even capturing the error makes the application essentially not work in a fundamental way.

And, not to be a curmudgeon, but, most apps don't really need that level of optimization. In 90% of apps it's fine. The 10% that really need the performance are hopefully doing it right with well tuned indices, careful denormalization, and other things. It is unimaginable to me, that someone with a higher performance SQL/RDBMS backed app can't run a query planner and tame ORMs for performance and identify things like wildcard column select. But what do I know :)

Very true. Also, along those lines, if you have fewer than 10k rows, it’s likely the dB will keep the whole table in memory after the first access, anyway.

In an ideal situation, agreed. But surely that depends entirely on how many tables are being queried (how many tables already exist in the cache), the size of the rows, how much RAM the host has / how big the cache is, plus a few other variables most probably.

What I mean is: like all other things, it depends. And with assumptions like this, it is generally better to measure.

he's not really talking about the performance of the DB though, focusing more on size of the resultant payload thatr gets sent over the wire. In the actual database which is traditionally very challenging to scale, projections aren't very expensive. The other things you and GP mention are way more likkely to move the performance needle.

Building software is making a series of trade-offs, but most tech articles present themselves as though they have the answer. It's not too surprising, then, that the top comments take opposing stances on an issue that is definitely more complicated than the article made it sound.

We have trade-offs but they are not all equal. It is both legitimate and encouraged to argue about which are the correct trade-offs IMO.

Agreed! The fact that the top two comments usually disagree is one of the primary reasons I read comments on HN. The dialectic allows me to make an informed decision later.

> accuses the author of saying something so obviously correct

Perhaps I do; but my main "accusation" here is that the title is pure click bait, if it was replaced by this:

     SQL - Don't fetch data you don't need!

Would anyone here bothered reading it?

This reminds me of a chilling short story I read a while back, but I cannot remember its name. It was about a machine learning algorithm trained on social network archives to take as input a body of dialogue between people and communities and to output statements which would strongly divide them. As a joke, the developers ran the algorithm on their internal corporate email and read the output sentences, laughing that anybody at their company could possibly come down on the other side of any of the resulting statements before realizing that they were talking about different sides. No longer being able to stand each other, the company dissolves. It was a good story if anyone recalls its name.

I just read it again recently. Shiri’s Scissor.

https://slatestarcodex.com/2018/10/30/sort-by-controversial/

That was a hell of a read. Thanks for the link!

That's it! Thanks so much.

Using select * can cause some correctness issues, but from a performance perspective, it’s basically an archetypal topic for bike shedding. In the sense that using the smallest projection you actually need is an optimization, but for most use cases it will end up being an irrelevant optimization. So select * is bad for performance, but you don’t need to care about it unless you’re experiencing one of the problems it case cause, which most people probably don’t. But you can have a back and forth discussions about that forever.

Well, the good news is that this comment cracked me up, thus demonstrating that this thread does provide some value to HN.

Cheers

This is because "strong opinions, lightly held" creates a culture where people with little to no insight or experience feel empowered to create an unceasing high-volume firehose of worthless opinions that drown out anything useful.

it does not invalidate his/her observation