[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

This email proudly and graciously contributes to entropy.

More information about the thelist mailing list