8 ms·
Myth: Select * is Bad
- groundCode 13y agoselect * can also be bad if you have any sort of system that expects or uses your columns by index. someone changes up your database, adds a column or some such and all of a sudden your consuming code breaks all over the place.
- nbouscal 13y ago> if you have any sort of system that expects or uses your columns by index. Of course if you have that sort of system, select * is the least of your worries.
- VLM 13y agoYou can also experience severe performance degradation (both server and client) if someone adds an absolutely huge binary column. Perhaps an example of table checking_account_register hmm lets add a new feature, after a paper check is cancelled we'll scan it into an image file and stick it in the database. Suddenly you get giant TIFF for each row returned, surprise! Of course a better spot Might be a separate scan table linking checks to an image of the check (perhaps multiple images, multiple scan attempts, multiple sides of the check, and all that), but for the sake of argument, etc. I think probably more databases get killed by processing load via no WHERE or LIMIT clause than from using a * as a column list... probably. With a close second of gathering way too much data and weeding it out in a HAVING.
- DrJokepu 13y agoOf course, adding large binary blobs to a relational database is almost certainly a bad idea anyway. It’s just not meant to be. You can achieve transactionality by other means and it won’t actually make managing and backing up data easier because suddenly database backups are an awful lot larger.
- deleted 13y ago[deleted]
- mistercow 13y agoIt seems to me that in discussions of NoSQL vs relational databases, one of the main arguments that comes up regarding ease of design and maintenance is "you can just use an ORM". But I see major disadvantage after major disadvantage of using an ORM, and there seems to be some overlap with the disadvantages of NoSQL (by which I am primarily referring to document-oriented databases). I really can't tell, once you factor these disadvantages in, what comes out on top.
- nbouscal 13y agoI don't think one is inherently superior to the other, it just depends on the data. If you have highly structured data for which integrity is mission-critical, you want to be using a RDBMS. If you have unstructured data for which throughput is mission-critical, you'll probably benefit from using a NoSQL system.
- andrewflnr 13y agoI'm sure someone has tried, but has anyone done a good job on an ORM that actually lets you select which columns to load and only selects those?
- fatalmind 13y agohere you go: http://use-the-index-luke.com/sql/join/hash-join-partial-objects#orm-partial-objects http://use-the-index-luke.com/sql/join/hash-join-partial-obj... It's really a PIA. Recently, I've learned about http://jooq.org/ http://jooq.org/ — not having checked in detail yet, it might be easier there.
- riffraff 13y agoDon't most ORM allow that? i.e. rails' ActiveRecord allows you to select columns easily in most cases: User.where(..) # all fields User.where(..).select(:name, :id) # only two of them joe.boards.select(:title) # only one in join joe.boards.pluck(:title) # only column value instead of full objects with one attribute set Though I recall it's involved for #belongs_to relations. You should also be able to setup the select as a default per-class, other than per query. I suspect, I likely didn't understand your question, could you expand it?
- bcoates 13y agoHe's missing the other half of why 'select *' is bad, which really is about the star: Selecting columns by name automatically makes almost all incompatible schema changes cause the query to fail (in an easily detected place, for an obvious reason), while still allowing non-breaking schema changes like adding or dropping columns you don't use to have exactly the same behavior as before.
- ams6110 13y ago'select <star>' also doesn't always do what you expect. I recall a problem with some Oracle views that were based on 'select <star>' queries. What we didn't realize is that the '*' is evaluated when the view is compiled, and if you later add columns to the underlying tables the view did not automatically include them. Edit: OK I don't know how to include a literal star in text here.
- TeMPOraL 13y agoTry typing 3 stars in a row: * :).
- ams6110 13y agoNo luck
- deleted 13y ago[deleted]
- nsmartt 13y agoThe following works without issues, for whatever reason: 'select *'
- thristian 13y agoI'm going to guess you need to backslash-escape your stars. EDIT: Nope, that wasn't it. shakes fist at crazy home-grown not-quite-Markdown
- 13y ago
- aidos 13y agoThere used to be a bug in either MySQL or SQL server (can't recall which anymore) where if you used select * in a view and then adjusted the schema the data would be out of line with the columns.
- nathanstitt 13y agoSQL Server and the 'feature' was still present in version 2003, not sure about version after that.
- codeulike 13y agoSQL Server behaviour is: If you have a "select *" view and then add more columns to underlying table, columns will not show up in view until view if refreshed Thats not too bad, and it does not get the column names mixed up.
- sciolistse 13y agoThat's the way it works now. In earlier versions any added columns would still be retrieved, shifting all the other column values to the right.. So all the data would wind up having the wrong column names.
- codeulike 13y agoI've been using MS Sql from version 7, through 2000, 2005, 2008 etc, and I never saw that bug.
- einhverfr 13y agoThat's also the way it works in PostgreSQL. I think the view is actually compiled into a parse tree with named columns, and the view given a defined type iirc. (Select * from ... in PostgreSQL is actually really usefun in anything object-relational because you get objects of a named type back btw.)
- PommeDeTerre 13y ago
- codeulike 13y agoHe says its a myth, then admits it isn't a myth. In conclusion: Clickbait for DBAs
- mistercow 13y agoThe original HN title, before it was "corrected" was "It's not about the star, stupid", which was less clickbaity.
- sjwright 13y agoLess clickbaity only because it was arguably meaningless in isolation. Was it an article about astronomy? Or perhaps online ratings systems?
- corresation 13y agoHis argument seems to be that it's a "myth" because you could do the same thing by explicitly listing out the columns. Which in no way makes the former a myth, and it is just pure clickbait, as you said. It gets much worse than just single table grab-alls, though. I once worked at a shop where they insisted upon prolifically using "select just about everything from every relational table, comprehensively joined to the n-th level" views as some sort of confused notion about code reuse, such that everything would then select from these master views. The end result was that it was incredibly frustrating trying to resolve performance problems, because literally everything was a performance problem. It was one of those instances where I shook my fists at every naive developer railing about purported premature optimizations.
- rhizome 13y agoThe more you implement techniques like master objects, the sooner you get to play with sharding! Mortgage-driven development: http://codemanship.co.uk/parlezuml/blog/?postid=147 http://codemanship.co.uk/parlezuml/blog/?postid=147
- mercurial 13y ago"Don't go out of your way to optimize prematurely" does not mean "architect your software in the opposite direction to industry common sense". "We will optimize later if needed" does not work at architecture level. That's why you prototype before committing yourself.
- erehweb 13y agoBad in column-oriented databases like Vertica.
- mrich 13y agoIt is bad because in a column store each column will be stored in a different location, resulting in seeks on disk or cache misses in memory. In a row store the complete row will often be in a contiguous block of memory. It gets worse when the column store uses dictionary compression and the dictionary is not already cached, since you need one more memory access to a random location. For wide tables the overhead is noticeable and the application should really only retrieve what is necessary.
- einhverfr 13y agoI use select * a lot in PostgreSQL. I do this any time I have a relatively self-contained table which can't be further normalized very far, and CRUD queries wrapped behind a stored procedure which returns the table type. The reason I use select * there is that it reduces a maintenance point when it comes to return types and tables. In that case, I am guaranteed to get a return type which matches the table type. If the two are closely linked, then select * makes a lot of sense, and the alternatives aren't going to do better. This makes schema changes more compatible than trying to change the column everywhere in every query in addition to the higher levels of code. I would generally agree that an application calling select * from table is a bad thing for the same reasons, but if you are encapsulating your db behind an API, a lot of reasons shift.
- jacques_chester 13y agoI have the opposite preference. If there are a big bag of breakages when I change the schema, at least I see where they are. I'm not a fan of relying solely on tests when the DB itself can notice introduced discrepancies.
- einhverfr 13y agoThe fundamental question is where and what you are doing. As I say, I use select * a lot, but these are in two groups: 1. In UDF's to ensure the proper return type, and 2. Against UDF's where it is the UDF designer's job to maintain the software interface contract. In those cases, select * works, but it only does so because you have dependency injection and can decide on a case-by-case basis whether to pass the changes up, wrap them in a view, or the like. Most of the time this is a simple choice. The app needs a new column that we are storing and so.... But in a multi-app environment there may be other choices, and that dependency injection makes all the difference in the world. EDIT: for example, if you have a UDF returning the type of a given table, then you may want to use select * because you have already decided these are to be tied together. Similarly select * from my_udf() is not a bad thing the way that select * from my_table is.
- 13y ago
- jacques_chester 13y agoThe main danger with mixing SELECT * and ORMs when you only need some particular columns is the performance hit. Not just on the database itself. You also need to ship surplus data to your application and then instantiate objects with surplus fields. All of this takes bandwidth, RAM and time. I also once saw someone write idiomatic ruby that performed a sum over an ActiveRecord .each. In dev, nobody noticed. In production it pulled a 9 million row table across, iterated over 9 million ruby objects, all to sum up a few hundred kb of actual data that the database could do in a few hundred ms. The problem is hoping that ORMs will make those icky databases go away. They don't. They can't.
- trollied 13y agoCrikey. SELECT * can be bad for the following reasons: - returning more data than you need over a network connection can be a bottleneck. The ORM/java prog written by a programmer that doesn't care may only need a few columns. Tables can be very wide, especially when CLOB/BLOBs are involved - Stored procedures, functions and triggers that use SELECT * may break horribly when columns are added or removed from tables, views or materialised views - Table types (collection and record types) will break if any column redefinition occurs - It shows a lack of understanding of the data by the developer. This may, in turn, cause further issues Proper schema design and understanding of the data involved is key when working with databases. The existence of NoSQL and the general dumbing down of Computer Science degrees, plus the teaching of ORMs has caused endless problems. The need for NoSQL in the first place was possibly caused by the use of high level programming languages where you didn't need to know the data - you just fetch it all, then piss around with it in ruby/whatever, then fetch it again & again until you're done. Rant over ;)
- acscott314 13y agoWhy is this article on HN? Come on. Select * is just a construct. When is it bad? That would be a better discussion. In any case the article demonstrates a lack of fundamental understanding of relation database technologies such as a clustered index...hm wait. I'm taking the bait. Read a good book on relational databases. SQL for Smarties will get you started. Is select count(1) from mytable faster than select count(*) from mytable?
- ronaldsvilcins 13y agoThanks for sharing your experience with Firefox OS.
- aray 13y agoWhat's the context to this? It doesn't make sense to me in relation to the article, but maybe I'm missing something.
- ck2 13y agoIf you have text fields in your columns and you don't actually need them, SELECT * in indeed bad as it will cause temporary tables to be made, even if indexes exist for the query.
- ohwp 13y agoSELECT * can be bad when you join other tables. For example: SELECT * FROM table1 JOIN table2 ON table2.field = table1.field In the above example you select everything from both table1 and table2. When they contain fields with the same name, strange things can happen. Therefore always use: SELECT table1.* FROM table1