in reply to Re: selecting again from a mysql database
in thread selecting again from a mysql database

"Never use SELECT * in code."

Along the lines of "never say never", I find this oft repeated bit of advice completely misleading. Certainly one can get in trouble with "SELECT *" and should know about its pitfalls. But there are many situations in which it is perfectly fine to use it and others in which it is the *only* thing to use.

Example 1: if you are using bind_columns (which is, after all, the fastest way to fetch), then this is fine: 

    my $sth = $dbh->prepare("SELECT * FROM foo");
    $sth->execute;
    my %row;
    $sth->bind_columns( \( @row{ @{$sth->{NAME_lc} } } ));
    while ($sth->fetch) {
        print "$row{region}: $row{sales}\n";
    }

[download]

Example 2: You want to display a table to examine it's structure visually, without knowing its structure in advance.

Should one be wary of code that expects the structure of a table to remain unchanged? Yep. Should one therefore never use "SELECT *"? Nope.

Replies are listed 'Best First'.
Re: Re: Re: selecting again from a mysql database
by hardburn (Abbot) on Nov 25, 2003 at 18:47 UTC

    if you are using bind_columns (which is, after all, the fastest way to fetch), then this is fine

    That's a reasonable example. It's still slower at the database level, though.

    You want to display a table to examine it's structure visually, without knowing its structure in advance.

    Looking over the structure of the data falls under interactive use (though you're probably better off using something like MySQL's describe [table] command), which is the reason why SELECT * works at all.

    If you're writing code for a real application, you ought to know what the columns are. If you don't, look them up. If you can't look them up, you've got other problems (either poor documentation or bad internal politics). The only exception I can think of to this is when you don't know what table you'll be using in advance (though I'm not sure about even that case).

    ----
    I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
    -- Schemer

    : () { :|:& };:

    Note: All code is untested, unless otherwise stated

[reply]
[d/l]
[select]
      That's a reasonable example. It's still slower at the database level, though.
      This may depend on the actual database you are using. I suspect that unless the table is really wide the difference in speed at the database level between "select col, ..." and "select *" is really minimal.

      After all, the SQL parser still has to resolve the columns that are passed in to the query and make sure that they exist, what type they are, etc.

      That said I agree that "select *" should be avoided in anything but throwaway code as it is generally an indication that the programer doesn't have a clear idea of what s/he wants.

      Michael

[reply]

In Section Seekers of Perl Wisdom