what is the speed hit of "SELECT *" with MySql, as opposed to narrower database calls?

[lkrubner]

Are there any benchmarks on how much an extra, unneeded VARCHAR, CHAR,
INT, BIGINT, TEXT or MEDIUMTEXT slows down a database call with MySql?
PostGre info would also be useful.

I'm trying to explain to some friends the utility of making database
calls return only needed data.

As an example of what I'm talking about, suppose we had a database
table sort of like this:

table weblogs (
int id,
varchar 255 headline,
text mainContent,
int dateCreated,
varchar 255 author,
varchar 255 navigationText,
char 1 isPrivate
);
Suppose I'm doing a PHP command that get's the headlines and offers a
link to the actual page. What is the speed difference between

SELECT * FROM weblogs

as opposed to

SELECT id, headline FROM weblogs

And is there info out there that gives a general sense of how much each
extra, unneeded database field might slow down a script?

[Malcolm Dew-Jones]

Alvaro G Vicario (al************ ******@telecomp uteronline.com) wrote:
: *** Andy Hassall wrote/escribió (Fri, 15 Apr 2005 20:01:06 +0100):
: >>The query itself is faster with *.
: >
: > Any evidence of that? That doesn't make a lot of sense to me.

: It's pretty obvious. Database server doesn't need to look for requested
: fields (I don't know how that works internally, but I'd say it needs to
: compare the strings of requested fields with every table field name), it
: just needs to fetch all columns.

I wouldn't make that assumption, though it may be true.

It is just as possible that the * is converted into a set of columns names
and then retrieved identically to the query that simply listed the column
names.

Personally I think the key factor is programmer productivity and code
correctness.

I find select * very useful because I don't have to decide until later
which columns I am going to use (in reports etc).

If fields are accessed by name then the code will not change if the
database changes.

More commonly, if the database changes then things like reports are
_supposed_ to change - but guess what - the report can incorporate those
changes by simply adding the fields, by name, into the output - none of
the underlying queries have to change. Which means less work and fewer
places for error.

I doubt that select * will introduce the type of fundamental inefficiency
that needs to be avoided at all cost, except in a few obvious cases.

$0.04 (inflation)

--

This space not for rent.

[Andy Hassall]

On Mon, 18 Apr 2005 10:01:12 +0200, Alvaro G Vicario
<al************ ******@telecomp uteronline.com> wrote:

*** Andy Hassall wrote/escribió (Fri, 15 Apr 2005 20:01:06 +0100):

The query itself is faster with *.
Any evidence of that? That doesn't make a lot of sense to me.

It's pretty obvious. Database server doesn't need to look for requested
fields (I don't know how that works internally, but I'd say it needs to
compare the strings of requested fields with every table field name), it
just needs to fetch all columns.

How does it know what "all columns" are? It's got to look them up. So you're
effectively back where you started.
Of course, when I say "faster" I just mean 1/1000 seconds.

All this affects is parsing time, which (should) be an insignificant
proportion of total query time. If it makes milliseconds worth of difference to
parsing time I would be surprised.

--
Andy Hassall / <an**@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

[CJ Llewellyn]

On Mon, 18 Apr 2005 10:02:58 -0800, Malcolm Dew-Jones wrote:

More commonly, if the database changes then things like reports are
_supposed_ to change - but guess what - the report can incorporate those
changes by simply adding the fields, by name, into the output - none of
the underlying queries have to change. Which means less work and fewer
places for error.

I doubt that select * will introduce the type of fundamental inefficiency
that needs to be avoided at all cost, except in a few obvious cases.

You've obviously not worked with some of the data sets and queries I've
worked with.

When a SQL report takes 20 minutes to run you know that it's down to bad
design and inefficient programming.

When I started working in IT storing more than three months worth of data
online was virtually unheard of. Now we expect everything to be kept
inpurpetual.

You might not regret your poor design decisions today, but seven years
down the line, you can almost be sure that it'll come back with an
avengance.

[Malcolm Dew-Jones]

CJ Llewellyn (cj**********@g mail.com) wrote:
: On Mon, 18 Apr 2005 10:02:58 -0800, Malcolm Dew-Jones wrote:

: >
: > More commonly, if the database changes then things like reports are
: > _supposed_ to change - but guess what - the report can incorporate those
: > changes by simply adding the fields, by name, into the output - none of
: > the underlying queries have to change. Which means less work and fewer
: > places for error.
: >
: > I doubt that select * will introduce the type of fundamental inefficiency
: > that needs to be avoided at all cost, except in a few obvious cases.

: You've obviously not worked with some of the data sets and queries I've
: worked with.

: When a SQL report takes 20 minutes to run you know that it's down to bad
: design and inefficient programming.

And if the select * is the cause of the slowdown then updating the query
to use exactly the columns required is about as trivial a bit of
maintenance as any I can imagine.

: When I started working in IT storing more than three months worth of data
: online was virtually unheard of. Now we expect everything to be kept
: inpurpetual.

And how does select * have anything to do with that?

: You might not regret your poor design decisions today, but seven years
: down the line, you can almost be sure that it'll come back with an
: avengance.

It's hard to imagine they could be a problem for seven years with no one
checking what was wrong, and just as hard to imagine that they could work
fine for seven years and suddenly become a problem.

I've seen a number of reports containing undetected logic flaws that are
used for some years before the error is detected. Compare that to an
inefficient query - it's pretty easy to detect, possibly easy to fix, and
is nothing more than an annoyance. I think that simple code that avoids
logic errors is much more important.
--

This space not for rent.

[Alvaro G Vicario]

*** Andy Hassall wrote/escribió (Mon, 18 Apr 2005 19:04:40 +0100):

All this affects is parsing time, which (should) be an insignificant
proportion of total query time. If it makes milliseconds worth of difference to
parsing time I would be surprised.

Yeah, I thought that was clear in my message.
--
-- Álvaro G. Vicario - Burgos, Spain
-- http://bits.demogracia.com - Mi sitio sobre programación web
-- Don't e-mail me your questions, post them to the group
--

[lkrubner]

I'm not sure I get you on this. If you're point is that one shouldn't
get fields one doesn't need, and therefore there is no need to compare
different fields, then we agree, but the agreement is sort of besides
the point. I was trying to explain to people who don't know much about
databases how each unneeded field slowed down a query, and I was
looking for some hard numbers to make my point. If a MediumText field
slows down a query more than a Char(1) field does, then that too would
help make my point.

While you're at it, why not compare the performance
between an Oil Tanker and a Canoe?

That is just what I'm trying to do, in a sense. An oil tanker can hold
more and go across a big ocean, a canoe, on the other hand, could make
its way down small creeks that an oil tanker would never be able to
traverse. Each of these things have different strengths and weaknesses,
and there is a different cost associated with each. In regards the
MediumText and Char fields, I was hoping to quantify the time cost
associated with each.

[lkrubner]

> And if the select * is the cause of the slowdown then updating the
query

to use exactly the columns required is about as trivial a bit of
maintenance as any I can imagine.

It wouldn't be trivial for a large corporate web design team where some
programmers were responsible for the database teir and others for the
template teir, though it wouldn't be any more complex than using
specific field lists from the start.