How Slow Is Select *?

A lot of the points in this article are far too sweeping to be true.<p>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&#x27;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.<p>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&#x27;s the right thing.<p>This absolutist sort of generalization that select * needs to be repaired everywhere is just silly.

1997... I&#x27;d built an extremely basic &#x27;ecommerce&#x27; type system, using php&#x2F;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&#x2F;upset&#x2F;angry&#x2F;etc, and I was trying to deal with hosting support. &quot;You&#x27;re running abusive software which is damaging our server(s), and we&#x27;ve disabled the account until you fix this&quot;.<p>Firstly, the account was completely disabled, so I couldn&#x27;t log in to even see what was wrong, or to even make a change once I might figure it out. Secondly, they wouldn&#x27;t tell me what the problem was. Eventually - 2 days later - someone from support said I were typing up their CPUs with my &#x27;shitty SQL&#x27;, and I should learn that &#x27;select *&#x27; was garbage, and my client should hire a &#x27;real developer&#x27;.<p>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&#x2F;account was back online, and, the real culprit was one of the other 200 accounts on that shared server - they were blasting out spam.<p>But hey... yeah, have someone grep for &quot;select *&quot; and take immediate action. That it didn&#x27;t actually stop the problem after a couple hours should have been the first clue.<p>That was my last direct experience with &#x27;shared hosting&#x27; and one of my first &quot;SQL snob&quot; encounters.

One thing I don&#x27;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&#x27;m selecting helps provide context for the code using that query.

Just keep in mind SELECT * and COUNT(*) are only visually similar.<p>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.

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

Select * is lazy and gives future developers (or even yourself later on) any hints about your intention for the query.<p>Never use *, you’ll thank yourself later on.

SQL queries really need to separate out the &quot;run the query&quot; from the &quot;now send me these details about the results&quot; part.<p>In many cases, I&#x27;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&#x27;ll be needing from the result set to avoid server round trips. (If I&#x27;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).