[thelist] merging tables in mysql

[Matt Warden]

On Fri, 20 Aug 2004 12:07:14 -0500, Peter Brunone (EasyListBox.com)
<peter at easylistbox.com> wrote:
>         First off, I'd recommend not using SELECT * since it can be a
> bit of a performance hit on higher-traffic sites.

Fwiw, it is only a performance hit if you do not need all the fields.
Depending on how smart the database optimizer is, if you explicitly
list all the attributes, it might actually do more work than if you
were to just use SELECT *. And, if you list the attributes in an order
other than the order in the actual relation, the database *will* have
to do more work.

That might be what you meant, but I see very often people suggesting
that SELECT * is bad in all cases, when in actuality it can save you
processing time. For example:

select foo, bar
from foobar
where exists (select 1 from barfoo where foo='bar');

or

select foo, bar
from foobar
where exists (select oneattribute from barfoo where foo='bar');

will take more time to process than the following:

select foo, bar
from foobar
where exists (select * from barfoo where foo='bar');

because the database will project the relation over 1 or oneattribute
before evaluating the exists clause.

There are many more instances where SELECT * is the better method to use.

People get confused because there is an *accurate* rule of thumb that
states that in a client-server design it is important to select only
the data (both tuples and attributes) that you need. This is becasue
the greatest cost is almost always the cost of tranferring the data
over the network. In this case, people suggest avoiding SELECT *
because programmers tend to use it even though they don't need one or
two of the attributes. Also, in the event the relation is altered
later to add attributes, you are then selecting data you do not need.





-- 
Matt Warden
Miami University
Oxford, OH
http://mattwarden.com


This email proudly and graciously contributes to entropy.