NYCPHP Meetup

[nycphp-talk] MySQL doubt

Hans Zaunere 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).


---
Hans Zaunere
President, Founder
New York PHP
http://www.nyphp.org

Gmail: The 1gb spam catcher 







More information about the talk mailing list