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

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?


I think you will find it isn't going to make much difference unless you are retrieving several million rows.

The amount of data read is dictated by the physical read characteristics of the disk subsystem. The database takes what the disk subsystem hands off to the OS, locates the desired table row, and then parses the row to pull out either each column or the specified columns.

The bulk of the time is spent waiting for disk i/o completion and the difference in time a high performance database like MySQL spends parsing the data and extracting and presenting either every column or a subset of the columns isn't significant.


BTW, I agree with Philip Nelson, who responded earlier, and felt that SELECT * should be avoided due to possible problems arising from future maintenance changes to the database.


HTH

Jerry

.