Re: SELECT almost every column

[Robert J Taylor]

John Mistler wrote:
Hmmm.  The reason I asked was that the last column in the table is TEXT, and
might contain up to 5000 text characters.  I'm trying to make the query as
efficient as possible, and I don't know if that much data will make a
noticeable speed difference?
Thanks,
John
on 5/14/04 2:31 PM, Justin Swanhart at [EMAIL PROTECTED] wrote:
 

John,
In that case even if you only grab one field in the select from the row 
the entire Text and/or Blob will be loaded into memory as a side benefit 
(</sarcasm>).

Assuming a table like:
   my_table(id, field1, field2, ..., field49, TEXTField)
You'll do much better by making two tables:
   my_table(id_field, field1, field2, ..., field49)
   my_table_text(id_field, TEXTField)
Then search on my_table and only pull my_table_text rows by exact match 
on the id_field (making both "id_field"s primary keys in their 
respective tables, of course).

HTH,
Robert J Taylor
[EMAIL PROTECTED]
--- John Mistler <[EMAIL PROTECTED]> wrote:
   

Is there a SELECT statement, if I have a table with
50 columns, to select
every column EXCEPT the last one?  Can I do this
without typing the name of
all 49 columns?
If so, then what if I want to exclude the last TWO
columns?
Thanks,
John
     

There is no construct in SQL to select "X number of
columns" from a table.
The traditional answer to this question would normally
be "use views", but since MySQL doesn't support them
that doesn't help you very much.
Unless the extra columns are long text columns or
contain BLOBS, then I see no harm in just selecting
them along with the rest of the other columns by using
"select * from"
If you are accessing the database from a programming
environment then you could do the following:
[pseudo code]
$sql = "desc $NAME_OF_TABLE"
$result = exec($sql)
$rows = fetch_result_into_array($result)
destroy($result)
$cnt = count($rows) - $NUMBER_OF_COLUMNS_TO_OMIT
if ($cnt <= 0) 
{  error("to few columns");
return;
}
$sql = "select "
for ($i=0;$i < $cnt-1;$i++)
{ $sql = $sql + $ary[$i]["Field"] + ", "
}
$sql = $sql + $ary[$cnt]["Field"]

$sql = $sql + " FROM $NAME_OF_TABLE_TO_SELECT_FROM"
$sql = $sql + " WHERE $WHERE_CLAUSE"
$sql = $sql + " HAVING $HAVING_CLAUSE"
$sql = $sql + " GROUP BY $GROUP_BY_CLAUSE"
$sql = $sql + " ORDER BY $ORDER_BY_CLAUSE"
   


 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]