3 ms·

I see this sentiment around a lot and can you explain why? I hope it’s more than just an optimization thing. Getting rid of an extra column or two strikes me as

I see this sentiment around a lot and can you explain why? I hope it’s more than just an optimization thing. Getting rid of an extra column or two strikes me as premature optimization in scenarios that are not “web scale”

blattimwind 8y ago
Writing "SELECT *" makes it less clear what columns are selected exactly. Explicitly writing what columns shall be returned is more explicit and clear.
- MichaelApproved 8y ago
  That's helpful from a code review perspective but it's the performance hit you get with a * select that is the bigger reason to avoid it.
  - dahdum 8y ago
    The column name conflicts can cause no end of trouble later on as well.
scrollaway 8y ago
The result format of select * is unpredictable unless you are guaranteed to know your current schema. And if you know your current schema and want all columns, for an ORM it's trivial to generate a list of all the columns you want, so there's no need to use star. For example: Let's say you have a piece of code that does: id, value = query("select * from tbl").fetch_one(). Now that works great if you know you have exactly the columns id and value in there. But let's say they swap places (possible in mysql), or a third column is added, and you don't update your code alongside it. Now that code breaks. Had you done "select id, value from tbl", it wouldn't break. PS: The sibling answers are also all correct; there's more reasons not to use select * outside of the shell.
- anderse 8y ago
  Do you mean your database driver doesn’t supply you with field names, that you rely on order to map up data?
  - scrollaway 8y ago
    Your driver can probably introspect, yes. You can also probably introspect yourself if you want to. But all this to be less explicit about what you're querying seems like a waste, doesn't it?
  - tejasmanohar 8y ago
    I guess I often rely on column order in Go, given Rows.Scan() [0]... so `select ` would be scary since someone may ALTER the table. Maybe that's the antipattern! But it's also a feature so I feel like you're better off avoiding the . Also, if someone adds additional big columns, you may incur additional, unnecessary cost [0]: https://golang.org/pkg/database/sql/#Rows.Scan https://golang.org/pkg/database/sql/#Rows.Scan
- paulmd 8y ago
  > for an ORM it's trivial to generate a list of all the columns you want, so there's no need to use star. But doctor, it hurts when I [don't use an ORM/ORM-generated queries]
  - scrollaway 8y ago
    If you're not using an ORM you especially shouldn't be using the star-op in code. Don't litter in the streets, don't litter in your code. If you make an unmaintainable mess and it hurts, you are entirely responsible for owning it.
- terandle 8y ago
  Gotcha yes for sure in this example you would want to use the column names. In my database code I extract values by referencing the column names - not by their position. So neither swapping or adding a new column breaks the code. Well thank you for explaining your reasons.
treebeard901 8y ago
When writing a query with multiple joins and functions, the query plan will build a pipeline that pulls the data from storage if it does not already exist in the cache. Then it has to process the data based on the declarative statement that was sent to the query engine. This means it may need temporary spools in memory or on disk to handle sorting, hash joins, etc. Sometimes the engine is able to optimize this pipeline and only pull in the columns used in the outermost part of the query. Being explicit in your column selection can greatly reduce the amount of memory and CPU needed to generate the results. Probably the worst impact this can have is when an application is using a query with SELECT * on the data side when only a handful are used in the application. Outside of disk IO, memory usage and CPU usage, now you have incurred an overhead by sending data across the network that is never being used.

blattimwind 8y ago

Writing "SELECT *" makes it less clear what columns are selected exactly. Explicitly writing what columns shall be returned is more explicit and clear.

MichaelApproved 8y ago
That's helpful from a code review perspective but it's the performance hit you get with a * select that is the bigger reason to avoid it.
- dahdum 8y ago
  The column name conflicts can cause no end of trouble later on as well.

scrollaway 8y ago

The result format of select * is unpredictable unless you are guaranteed to know your current schema. And if you know your current schema and want all columns, for an ORM it's trivial to generate a list of all the columns you want, so there's no need to use star. For example: Let's say you have a piece of code that does: id, value = query("select * from tbl").fetch_one(). Now that works great if you know you have exactly the columns id and value in there. But let's say they swap places (possible in mysql), or a third column is added, and you don't update your code alongside it. Now that code breaks. Had you done "select id, value from tbl", it wouldn't break. PS: The sibling answers are also all correct; there's more reasons not to use select * outside of the shell.

anderse 8y ago
Do you mean your database driver doesn’t supply you with field names, that you rely on order to map up data?
- scrollaway 8y ago
  Your driver can probably introspect, yes. You can also probably introspect yourself if you want to. But all this to be less explicit about what you're querying seems like a waste, doesn't it?
- tejasmanohar 8y ago
  I guess I often rely on column order in Go, given Rows.Scan() [0]... so `select ` would be scary since someone may ALTER the table. Maybe that's the antipattern! But it's also a feature so I feel like you're better off avoiding the . Also, if someone adds additional big columns, you may incur additional, unnecessary cost [0]: https://golang.org/pkg/database/sql/#Rows.Scan https://golang.org/pkg/database/sql/#Rows.Scan
paulmd 8y ago
> for an ORM it's trivial to generate a list of all the columns you want, so there's no need to use star. But doctor, it hurts when I [don't use an ORM/ORM-generated queries]
- scrollaway 8y ago
  If you're not using an ORM you especially shouldn't be using the star-op in code. Don't litter in the streets, don't litter in your code. If you make an unmaintainable mess and it hurts, you are entirely responsible for owning it.
terandle 8y ago
Gotcha yes for sure in this example you would want to use the column names. In my database code I extract values by referencing the column names - not by their position. So neither swapping or adding a new column breaks the code. Well thank you for explaining your reasons.

treebeard901 8y ago

When writing a query with multiple joins and functions, the query plan will build a pipeline that pulls the data from storage if it does not already exist in the cache. Then it has to process the data based on the declarative statement that was sent to the query engine. This means it may need temporary spools in memory or on disk to handle sorting, hash joins, etc. Sometimes the engine is able to optimize this pipeline and only pull in the columns used in the outermost part of the query. Being explicit in your column selection can greatly reduce the amount of memory and CPU needed to generate the results. Probably the worst impact this can have is when an application is using a query with SELECT * on the data side when only a handful are used in the application. Outside of disk IO, memory usage and CPU usage, now you have incurred an overhead by sending data across the network that is never being used.