[thelist] MySQL oddness

Matt Warden mwarden at gmail.com
Tue Jan 29 11:02:54 CST 2008


On 1/29/08, Raoul Snyman <raoul.snyman at saturnlaboratories.co.za> wrote:
> BTW, rule #1 of writing decent SQL: Never use SELECT *
> This causes MySQL to first do a look up on all the columns of the table,
> before then using them in the SELECT statement. When you specify the
> columns, you firstly reduce the time it takes to execute the query and the
> load on the MySQL server, and secondly you narrow the data down to only
> that which you really need.

Do you have a source for this? Unless there is some optimization in
MySQL that I don't know about, this isn't quite how it works. SELECT *
gets you into trouble when (a) you could have used an index if you
specified only the columns you needed, or (b) you will be transferring
the resulting data over a network (which is most of the time) and
could have benefited from a smaller data file size.

Specifying the exact columns you want actually causes the database to
do MORE work than selecting all the columns.

So, point is, I agree with your suggestion to avoid SELECT *, but I
don't necessarily agree with your reasoning, as query execution time
will increase.

-- 
Matt Warden
Cincinnati, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.



More information about the thelist mailing list