<story><title>Ways to Tweak Slow SQL Queries</title><url>http://www.helenanderson.co.nz/sql-query-tweaks/</url></story><parent_chain><item><author>craig_asp</author><text>&amp;quot;Pull out the names of only the columns you need instead of using SELECT * to speed things up further.&amp;quot;&lt;p&gt;This is not performance-related in most cases. Unless the bottleneck is in the amount of data being transferred to a client over a network connection and there is a large amount of columns, or you have an index which matches the limited column list query exactly, there would be no performance difference in SELECT * vs SELECT &amp;lt;column list&amp;gt;.&lt;p&gt;In columnar dbs it does matter because the less columns you select the less data gets accessed on disk. However, this does not hold true for row stores because data is stored in such a way that the whole row gets accessed no matter how many columns get specified in the query.&lt;p&gt;There are many other good reasons why SELECT * is acceptable only in development queries, but performance is not one of them.</text></item></parent_chain><comment><author>branko_d</author><text>Some joins may be completely eliminated if the fields coming from them are omitted from the SELECT list. This happens when the DBMS can prove (through FOREIGN KEYS) that the joined row always exists, without having to ever physically retrieve that row.&lt;p&gt;This is especially important when querying views (or inline functions, if your DBMS supports them) built on top of several other layers of views.</text></comment>
<story><title>Ways to Tweak Slow SQL Queries</title><url>http://www.helenanderson.co.nz/sql-query-tweaks/</url></story><parent_chain><item><author>craig_asp</author><text>&amp;quot;Pull out the names of only the columns you need instead of using SELECT * to speed things up further.&amp;quot;&lt;p&gt;This is not performance-related in most cases. Unless the bottleneck is in the amount of data being transferred to a client over a network connection and there is a large amount of columns, or you have an index which matches the limited column list query exactly, there would be no performance difference in SELECT * vs SELECT &amp;lt;column list&amp;gt;.&lt;p&gt;In columnar dbs it does matter because the less columns you select the less data gets accessed on disk. However, this does not hold true for row stores because data is stored in such a way that the whole row gets accessed no matter how many columns get specified in the query.&lt;p&gt;There are many other good reasons why SELECT * is acceptable only in development queries, but performance is not one of them.</text></item></parent_chain><comment><author>danielbarla</author><text>In terms of performance, the primary rationale for only retrieving columns you need would be to allow the database engine to select a proper index to use, specifically, one that covers all the columns you are making use of.  This can actually have a massive impact on the execution plan, far more than simply fetching say 2x more data, as you point out.&lt;p&gt;In fact, I&amp;#x27;d go so far as to say that the practice of selecting all columns makes it practically impossible to make use of covering indexes for high impact queries.</text></comment>