[thelist] MySQL oddness

[Phil Turmel]

Hi Matt,

Matt Warden wrote:
> On 1/29/08, r937 <rudy at r937.com> wrote:
>> on the other hand, specifying SELECT * when you don't need
>> all the columns is what will increase query execution time,
>> and the difference can be substantial
> 

Rudy's point about an extra round-trip to the server during
parsing is orthogonal to the server's query execution plan.
Since typical simple queries execute in fractional milliseconds,
and round-trip latencies for typical ethernet transactions
(TCP/IP) tend to approach or exceed one millisecond, an
additional round trip can double your query execution time.

An application with wildcarded select lists developed on an
integrated server stack (LAMP) will suffer in production if the
database is on a different box.  Sometimes the bandwidth needed 
for the extraneous columns is also significant.

> Rudy, this is the part I am contesting (in fact I had not even
> thought about the parsing difference). Specifying a subset of
> columns will require a projection[1]. Or are you suggesting
                ^^^^^^^
Not true.

> that the database will perform a projection even if the column
> list is the full set?
> 
> [1]
> http://en.wikipedia.org/wiki/Projection_(relational_algebra)
> 

Projection is merely an implementation of DISTINCT or GROUP BY.
If your query doesn't call for it, you might get duplicate rows.
If your query does call for it, which columns are involved 
affects the results.  If your application needs projection for
correctness, you'd better be explicit.

HTH,

Phil Turmel

-- 
Need to contact me offlist?
   Drop -webdev or you probably won't get through.