How Slow Is Select *? | Hacker News Reader

How Slow Is Select *?

vettabase.com

A lot of the points in this article are far too sweeping to be true.

For instance, the statement that using select * will cause too many columns to be read is repeated over and over, but it mostly just isn't true. For instance, if I define a view that does select * from a join of two tables and then do a query that asks for two columns from the view, essentially every optimizer on the planet will push down the two columns and will never retrieve all the columns. The same applies to common table expressions.

Moreover, putting a * in these views or common expressions is actually less error prone than putting in an explicit field list. What you are saying is that the view or expression is passing everything through from a join or filter operation. That's the right thing.

This absolutist sort of generalization that select * needs to be repaired everywhere is just silly.

(At least in Oracle) Rows are held in blocks, and the whole block must be read to retrieve a row within it. If the row entirely fits, there is no difference between selecting a subset of columns versus all of them, as the disk must do the same work. When columns are sharded over several servers, this would not hold true.

Never use SELECT * within the context of an application, as column additions or removals can break the app. It pays to be specific in code.

For interactive use, it's fine, especially when the exact spellings of the target columns is not known. The author might have more of a point on complex views.

> Never use SELECT * within the context of an application, as column additions or removals can break the app.

Use a dictionary cursor, or some other method to convert whatever is returned into a hashmap like so: https://dev.mysql.com/doc/connector-python/en/connector-pyth...

Not good enough, as 'select *' can in any event introduce ambiguity that is fatal to query processing.

It actually appears to go the other way. Explicitly referencing an ambiguous column causes the error while using select * avoids it, at least with duckdb (which is a very standard sort of SQL). This suggests to me that using select * in views is a very good practice.

  D create table a (amb integer, a1 integer, a2 integer);
  D create table b (amb varchar, b1 integer, b2 integer);
  D insert into a values (1,1,1);
  D insert into b values ('b',2,2);
  D insert into b values ('b',1,3);
  D select * from a, b where a.a1 = b.b1;
  ┌─────┬────┬────┬─────┬────┬────┐
  │ amb │ a1 │ a2 │ amb │ b1 │ b2 │
  ├─────┼────┼────┼─────┼────┼────┤
  │ 1   │ 1  │ 1  │ b   │ 1  │ 3  │
  └─────┴────┴────┴─────┴────┴────┘
  D select amb from a, b where a.a1 = b.b1;
  Error: Binder Error: Ambiguous reference to column name "amb" (use: "b.amb" or "a.amb")

The database query analyzer is doing you a favor by telling you there's an ambiguous column name and refusing to run, for the same reason a compiler won't allow you to define the same variable name twice.

Do you have an example to back that up?

How does that help against columns being added or removed?

If they are added they are just another key in the hashmap, no harm. If they are removed, I don't have a solution for that. Typically you don't remove columns of databases like that, but adding them is common.

I suspect that almost any sweeping statement that Oracle doesn't support some feature or other is wrong in the current day.

Oracle can certainly handle columnar formatted data. The in-memory columnar format is just what it says, HCC provides columnar compression and Exadata has done columnar data access forever.

I know little about Oracle specifically, but I do know that it is a huge beast with all kinds of feature that make generalization difficult.

> Never use SELECT * within the context of an application, as column additions or removals can break the app.

To help with this problem, several databases now support a notion of "invisible columns", including Oracle, MySQL, and MariaDB. Invisible columns are excluded from SELECT *, but otherwise function like normal columns and can be queried explicitly by name.

1997... I'd built an extremely basic 'ecommerce' type system, using php/fi and mysql... 3, IIRC (slight chance this might have been mSQL). This was run off a shared hosting system, and... after running for a couple months... it was suspended. The client was ... livid/upset/angry/etc, and I was trying to deal with hosting support. "You're running abusive software which is damaging our server(s), and we've disabled the account until you fix this".

Firstly, the account was completely disabled, so I couldn't log in to even see what was wrong, or to even make a change once I might figure it out. Secondly, they wouldn't tell me what the problem was. Eventually - 2 days later - someone from support said I were typing up their CPUs with my 'shitty SQL', and I should learn that 'select *' was garbage, and my client should hire a 'real developer'.

So... the server is having huge CPU spikes and clogging their network switch (got this over another couple days from another support person). After 8 days, the site/account was back online, and, the real culprit was one of the other 200 accounts on that shared server - they were blasting out spam.

But hey... yeah, have someone grep for "select *" and take immediate action. That it didn't actually stop the problem after a couple hours should have been the first clue.

That was my last direct experience with 'shared hosting' and one of my first "SQL snob" encounters.

One thing I don't like about using `SELECT *` in code is that it hides information from the your future self. When I come back to a piece of code in a couple of years time, knowing which columns I'm selecting helps provide context for the code using that query.

Yes. But there are times it is by design for the sql query to be agnostic about the database structure or the data model. That's when `SELECT *` becomes very useful.

People say what they will with JPA but at least it makes “context for the code” very clear.

Just keep in mind SELECT * and COUNT(*) are only visually similar.

The first one needs to read all data if used at the top level and the second one needs no actual data - just the overall row count, in whatever way is most optimal to calculate.

I think this belief is a remnant of old versions of MySQL which traversed the entire table counting rows. This is mostly in the past though as they'll typically use the primary key index to count rows or just save the number somewhere (depending on the DBMS)

InnoDB (the most common storage engine in MySQL) uses a clustered index. In other words, the "primary key index" IS the table. Also a COUNT(*) query has to account for MVCC, ensuring that the count represents an accurate total for a specific isolated snapshot.

So this is basically still true today, although MySQL 8 has started to add parallelized reads to improve performance for this situation.

Do you have a source for this? This is relevant to my work, but I don't know much about the implementation details of MySQL.

In my experience (long years as consultant/admin in the MSP business), it's always the database server or the network that is too slow, and it's never the SQL query or missing indexes. Cough magento cough </s>

Select * is lazy and gives future developers (or even yourself later on) any hints about your intention for the query.

Never use *, you’ll thank yourself later on.

SQL queries really need to separate out the "run the query" from the "now send me these details about the results" part.

In many cases, I'd like that latter data transfer stage to be smarter. Give me back some smart object which represents a result set, but then lazily only retrieves the information my code actually uses. At the same time, try to predict what information I'll be needing from the result set to avoid server round trips. (If I'm iterating through a result set in order accessing 4 of the 8 columns, you can probably just batch send the data my code uses).

https://www.postgresql.org/docs/14/plpgsql-cursors.html

Cursors are a for rows at a time. Sounds like above wants `Select *` but only magically pick the columns/fields used by the code.

Which would be possible, but not without the server round trips that he also seems to want to get rid of.

Basically, he wants a bunch of complexity and heuristics to be pushed into the query planner, the client, and the protocol, just so he doesn't have to write the column names.

Reply on news.ycombinator.com