NYCPHP Meetup

[Analysis & Solutions]

Hi Folks:

On Tue, May 27, 2003 at 12:51:01PM -0400, Malcolm, Gary wrote:
> 1. don't use count(*) because mysql will pull all fields for every record...
> 	use $result = mysql_query("SELECT count(col_name) FROM table_name");

That's not the case.  count(*) is preferable.  The manual says:

"COUNT(*) is somewhat different in that it returns a count of the number
of rows retrieved, whether they contain NULL values. COUNT(*) is optimised
to return very quickly if the SELECT retrieves from one table, no other
columns are retrieved, and there is no WHERE clause. For example:

mysql> SELECT COUNT(*) FROM student;

( from http://www.mysql.com/doc/en/Group_by_functions.html )
( see also http://www.mysql.com/doc/en/Counting_rows.html )


> From: Nasir Zubair [mailto:lists at ny-tech.net]
> >
> > $result = mysql_query("SELECT col_name FROM table_name");
> > $count = mysql_num_rows($result);
> >
... OR ...
> >
> > $result = mysql_query("SELECT count(*) FROM table_name");
> > list($count) = mysql_fetch_row($result);

Now, which is better in your case depends on what you're doing.  If you
JUST want to find out the rows, the latter is better.  If you're getting a
bunch of data which you then intend to output, doing one query and then
using mysql_num_rows() on it is probably more efficient.

--Dan

-- 
     FREE scripts that make web and database programming easier
           http://www.analysisandsolutions.com/software/
 T H E   A N A L Y S I S   A N D   S O L U T I O N S   C O M P A N Y
 4015 7th Ave #4AJ, Brooklyn NY    v: 718-854-0335   f: 718-854-0409