[nycphp-talk] MySQL doubt
zaunere at gmail.com
Fri Jan 14 11:50:42 EST 2005
> > I'm not sure whether i should post this here. Correct me if i'm
> > wrong.. I would like to know
> > whether there is any performance difference between. "SELECT count(*)
> > FROM table" and "SELECT count(field) FROM table".
Those queries are the same performance wise. Adding in a GROUP BY, however,
could change the query.
> Mysql has different table types. MyISAM tables always know the number
> of rows within the table. The result of "SELECT (*) from table" is
> always known and should return instantly.
Correct... as part of the MyISAM data structure, the number of rows is kept.
However, using a GROUP BY would still cause for a calculation in some cases.
> This may not work on other table types however. For instance, it does
> not work for InnoDB tables.
These still all work for InnoDB, however InnoDB doesn't keep the number of
rows as part of it's data structure. So, for a MyISAM table:
SELECT COUNT(*) FROM mytable
Will always return instantly, whereas for a InnoDB table, it actually has to
make the count (which makes sense, since InnoDB is ACID transactional).
New York PHP
Gmail: The 1gb spam catcher
More information about the talk