Why SELECT * is bad for SQL performance (2020)

I agree wildcards are better avoided but so are column orders in result extraction. They are a source of bugs and much review work when changes are made.

If I had to choose (which usually isn't necessary) I'd say wildcards are the lesser evil.

"care about the order of your columns" is a far bigger red flag than "select *".

Unless I'm misunderstanding, what you're talking about there isn't wildcard selects but rather SELECT's without any filtering.

ie having `SELECT id, firstname, surname FROM people` would still catch 'A1234567890' and '123Smith!!!'

Where as `SELECT * from people WHERE id REGEXP '^[0-9]+$'` would exclude them despite being a wildcard SELECT.

Ummm... you seem very confused... The "select" part of the query solely filters on columns, not rows. You are talking about filtering on rows.

Beyond that, when you are looking for anomalous conditions you don't say "get me all records," because how you going to pick out something that doesn't look right out of five million items? When you are looking for anomalous conditions you say "get me all records that are NOT in the form I am expecting." Then you add constraints, foreign keys, etc so it doesn't happen again.

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

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.

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

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.

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

Cheers

it does not invalidate his/her observation

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.

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.

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.

That's not a mistake at all. It's the simplest code that loads the data you want. 99.9% of cases the "harm" is insignificant. Anything more complicated is premature optimization.

It's a tradeoff. You might want to encapsulate database access and deal with objects instead of relations in most of your stack, which leads to these inefficient access patterns. Usually it doesn't matter. And if it does matter, you are probably doing something harmful like querying in a loop, n+1 etc.

Really? In my experience adding columns to a production database table is pretty infrequent, and even when it happens it's usually only one or two. (It's often a pretty big headache to add columns in practice.)

I've worked on a lot of databases and I've never seen the number of columns grow to the extent that it would impact performance in a large way. It might go from 8 to 11 columns, but I've never experienced anything like 5 to 55.

For a variety of reasons, if there's a whole new set of data (like customer survey responses to be associated with the customers table), or large data (like product thumbnails to be associated with the products table), to be associated with primary keys, it's added to an entirely new table that you can perform a JOIN on only when required.

Not to mention that while any application client can often add new rows to a database, they can't generally add new columns -- whoever administers the database often keeps those privileges for themselves for general security reasons.

That's fair, but some database systems have added functionality to avoid this specific problem. In Oracle, MySQL, and MariaDB, you can mark a column as INVISIBLE to exclude it from SELECT *.

It's still not a great practice to use SELECT *, but at least this gives you a way to add columns without breaking existing applications. It's also useful when you plan to remove a column, to test in advance whether anything using SELECT * would break without that column there.

Yes, and this is the biggest non-performance reason that wildcards are bad: because over time, the schema should be able to change, often in ways that render select * (and the equally works-only-by-accident code calling it) broken.

This is even more so when joins or views are involved, because the columns returned may change over time; joins particularly. Or, for that matter, migrations; while a CREATE plus ALTER may have resulting in a particular column ordering in 2002-2017, the new DB using the CREATE that rolls up all those changes in 2018 may result in a different column ordering, with subsequent broken code (which will be blamed on the DB by the thoughtless programmer, of course).

This performance optimization is valid for most databases:

COUNT(column) means in reality COUNT(column is NOT NULL), so if the column can have nulls the values need to be checked. But in most databases there is no difference in performance because the query optimized is intelligent enough to check whether the column can have null by definition and switches to COUNT(*) if there can't be nulls. In some databases COUNT(primarykey) is even faster, but these are all optimizations most often not needed, so micro optimizations as the query optimizer is intelligent enough to choose the best logic ;)

Bigquery is surprisingly dumb when it comes to rearranging your query to be able to execute it faster. It seems to entire focus of the bigquery team is being able to parallelize all the work to be done, rather than seeing how they could do less work in the first place. The former is clearly interesting when you bill per Gigabyte of data processed...

I suspect that stems from the fact it doesn't have the pregenerated statistics that other databases have, and therefore there isn't much scope to make a smart query optimizer.

The second part is due to that count(*) would actually be better written as count() if it was not for the weirdness of the SQL standard.

Yes? Most web frameworks do include some form of ORM.

Anecdotally yes, most small to medium sized CRUD apps do use either an ORM or a query builder so abstracted as to be effectively an ORM.

Wouldn’t that only apply if the query or the view features a DISTINCT or GROUP BY, and no COUNT()? Otherwise you’d still need to know how many rows in the other table match the predicate, so you’d still need to do the join, right?

But that argument only holds true of you rely on the column order. Most modern applications just specify column names where this doesn't matter.

A lot of best practices from 20 years ago are no longer valid. Like the excessive normalisation. These days data storage isn't always the major limiting factor anymore, in many cases it's better to have less tables with more data.

In most cases it is better to let application fail as early as possible when something unexpected occured. By making it more stable that way (SELECT *), you create hidden bugs which hard to detect and recovery is more expensive. It is even more important for data processing pipelines. I add assert/except to my code when it is possible: fail job, analyze, fix/report bug, notify downstream, rerun.

I see that, too. But I don't think it's fair to blame on ORM. I also see it happening in non-ORM code that uses the repository pattern, for example.

For example, there's nothing about the code example you give that strictly implies the use of an ORM, just the use of some sort of layered design.

This is not necessarally terrible in some scenarios: namely, when the repository caches enties for the duration of the unit of work, and the user in question is used elsewhere in the request (or at least is used elsewhere in the common case).

In that scenario, this can be strictly more efficient than doing a specialized query here, and a more general fetch of the user later, because it becomes just one sql command instead of two.

Obviously though there are ORMs that don't offer such caching, or cases where the value will not be used again elsewhere in the request, and in those cases this is clearly undesirable. It is generally quicker and easier to do this than adding a new custom method to the repository to get exactly the desired data which is why it remains common even in those scenarios.

On the other hand, if the user object was already recently used somewhere else it will be cached and this code will produce no database/network traffic at all.

Maybe you're relying on your ORM object to have all columns available, even when you add more, and you're in for a good time.

> but I think it's a solid principle that you shouldn't assume your database will retain its schema indefinitely.

this is not a hard thing to anticipate and yet my coworkers thought otherwise...

And when you add columns you need to a table, you will also need to add them to your query.

But I didn’t say it’s a good idea to do SELECT * (I never use it outside manual queries), just that it has no performance implication just by virtue of using it.

In some cases its not that simple, for example with Hibernate if you select specific columns then the object you get back won't be put in its L1 cache because its not the full object.

So in some cases its better to select the whole object by primary key because some other method would do that later anyhow and now its in the cache and will skip an extra db call.

That made me realize how much I missed .NET and my favorite tool LINQPad. Add Dapper next to your advanced ORM and you're basically unstoppable.

I was about to post the same thing. This works perfectly fine for EF Core, as long as you're aware that the mapping has to be directly in the Select like this so that it can be translated to SQL.

I’m not saying developer-friendly APIs are bad, but i do think hand-crafted, well-written, and well-tested SQL outperforms any ORM, and if any ORM comes close to the performance of hand-crafted queries, it does so at the cost of complexity.

Raw database queries aren’t difficult except in extreme edge cases that most ORMs aren’t smart enough to handle either. ORMs do make things slightly “easier”, but that comes at a cost. Whether that cost is in terms of performance or complexity or developers losing understanding/knowledge of how to build code that leans into the benefits of whichever database you choose comes down to whichever ORM you’re using, but that trade off will always be there.

And either way, 99% of people using any random ORM have no idea whether a `select *` is being used or not. That’s the whole point. You put blind faith into whatever ORM believing it will do the “right”/“most optimized” thing.

Knowing whether to `SELECT *` vs `SELECT a,b,c` is the entry-level/babys-first-optimization case. Providing a high level and performant ORM, takes deep knowledge of the underlying database and a lot of code complexity. But that wasn’t really my point. My point was:

If you’re using an ORM this whole article is moot to you. You don’t decide whether `SELECT *` is the being used or not (let alone more complex optimizations), and if you do actually delve this deep into your ORM you are in the vast minority of coders

if you’re using an ORM it’s an architectural decision that was mandated early on in whatever project, so even if you did find out a naive `select *` is being used by your third-party ORM, the whole point would be moot because we can’t just switch out ORMs for this project.

So for most people using an ORM, this is useless information because either you don’t know/care or your organization won’t let you know/care because they’re already doing it that way organization-wide.

> Use a better ORM.

This so many times. I also think before people talk about an ORM they need to define ORM as they can range from something like Hibernate to a simple object mapper. Is something like jOOQ and ORM where I can write typed SQL and it maps results to objects?

I prefer writing SQL, but also prefer using a library that can do the tedious object mapping for me.

I see so multiple page loads are also possible. Is the threshold documented ? Say for example for postgres ?