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

On 9 Apr 2005 12:40:13 -0700, lkrubner@xxxxxxxxxxxxx wrote:

>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?

Well, It Depends.

How wide is the row? If you've got a LONGTEXT in there with 1Mb of data, the
impact is going to be far greater if you fetch it when you don't need it.

What's your query? I don't know if MySQL has this feature, but in Oracle,
let's say you query just primary key columns; this can be answered more quickly
by scanning the index rather than the table (since the index is smaller), which
eliminates lots of lookups from the index to the table itself. But if you
request columns that aren't in the index, then it's got to hit the table to get
the data. So the same query conditions with a different select column list can
be executed very differently.

--
Andy Hassall / <andy@xxxxxxxxxxx> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
.

Relevant Pages