5 ms·

How Slow Is Select *?

scrapheap 5y ago
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.
- flatiron 5y ago
  People say what they will with JPA but at least it makes “context for the code” very clear.
- temp8964 5y ago
  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.
londons_explore 5y ago
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).
- jayski 5y ago
  https://www.postgresql.org/docs/14/plpgsql-cursors.html https://www.postgresql.org/docs/14/plpgsql-cursors.html
  - djbusby 5y ago
    Cursors are a for rows at a time. Sounds like above wants `Select *` but only magically pick the columns/fields used by the code.
    - mike_hock 5y ago
      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.
ted_dunning 5y ago
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.
- chasil 5y ago
  (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.
  - willcipriano 5y ago
    > 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-python-api-mysqlcursordict.html https://dev.mysql.com/doc/connector-python/en/connector-pyth...
    - sk5t 5y ago
      Not good enough, as 'select *' can in any event introduce ambiguity that is fatal to query processing.
geoffmunn 5y ago
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.
cube2222 5y ago
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.
- booi 5y ago
  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)
  - alecst 5y ago
    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.
  - evanelias 5y ago
    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.
mgkimsal 5y ago
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.
reph2097 5y ago
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>

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.

flatiron 5y ago
People say what they will with JPA but at least it makes “context for the code” very clear.
temp8964 5y ago
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.

londons_explore 5y ago

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

jayski 5y ago
https://www.postgresql.org/docs/14/plpgsql-cursors.html https://www.postgresql.org/docs/14/plpgsql-cursors.html
- djbusby 5y ago
  Cursors are a for rows at a time. Sounds like above wants `Select *` but only magically pick the columns/fields used by the code.
  - mike_hock 5y ago
    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.

ted_dunning 5y ago

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.

chasil 5y ago
(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.
- willcipriano 5y ago
  > 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-python-api-mysqlcursordict.html https://dev.mysql.com/doc/connector-python/en/connector-pyth...
  - sk5t 5y ago
    Not good enough, as 'select *' can in any event introduce ambiguity that is fatal to query processing.

geoffmunn 5y ago

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.

cube2222 5y ago

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.

booi 5y ago
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)
- alecst 5y ago
  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.
- evanelias 5y ago
  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.

mgkimsal 5y ago

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.

reph2097 5y ago

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>