automating the construction of faster queries

After reading somewhere in one of these NGs that naming all the fields
in a query produced faster searches than "SELECT *..." I've rewritten
my queries as follows:

$table = words;
$describe = "DESCRIBE $table;";
$field_array = mysql_query($describe) or die ("Couldn't execute
query.");

while ($row = mysql_fetch_assoc($field_array))
{
$fields .= $row["Field"] . ", ";
}
$fields = substr($fields, 0, -2); //trim last comma

echo $fields;

$query = "SELECT $fields FROM $table etc, etc"

This works but looks extremely long-winded. I bet there's a much more
efficient way of doing this, isn't there?

Re: automating the construction of faster queries

strawberry wrote:
> After reading somewhere in one of these NGs that naming all the fields
> in a query produced faster searches than "SELECT *..." I've rewritten
> my queries as follows:
>
> $table = words;
> $describe = "DESCRIBE $table;";
> $field_array = mysql_query($describe) or die ("Couldn't execute
> query.");
>
> while ($row = mysql_fetch_assoc($field_array))
> {
> $fields .= $row["Field"] . ", ";
> }
> $fields = substr($fields, 0, -2); //trim last comma
>
> echo $fields;
>
> $query = "SELECT $fields FROM $table etc, etc"
>
> This works but looks extremely long-winded. I bet there's a much more
> efficient way of doing this, isn't there?

Yes -- tell it exactly which columns you want. The reason select * is
slow is because you're getting /all/ the columns. If a table has 20
columns and you only need 3, then why ask for all of them?

Re: automating the construction of faster queries

You miss my point. I DO want ALL the columns. It's just that (from what
I've read) the query is supposed to work faster if ALL the columns are
individually named in the query.

So, if that's true - and with that fact in mind - is the code I've
written the simplest way of going about this or is there (as I'm sure
there must be) a better way?

For instance, one of my queries requests some 100 or so fields from a
contacts database of 1000 or so records. I rather like the idea of
rewriting the query somewhat like that shown above - if it really is
the case that doing so will considerably increase the speed at which
the query executes.

Re: automating the construction of faster queries

strawberry wrote:
>
> You miss my point. I DO want ALL the columns. It's just that (from what
> I've read) the query is supposed to work faster if ALL the columns are
> individually named in the query.
>
> So, if that's true - and with that fact in mind - is the code I've
> written the simplest way of going about this or is there (as I'm sure
> there must be) a better way?

There is. Use SELECT *. Even if SELECT * is somewhat slower than
SELECT [fields], it's still one query. In your solution, there are
two. Since there is a fixed per-query overhead, your solution is
probably worse than the problem you are trying to solve...

Cheers,
NC

Re: automating the construction of faster queries

Post removed (X-No-Archive: yes)

Re: automating the construction of faster queries

Thanks guys.