NYCPHP Meetup

[Sexton, David]

>>>and there is no WHERE clause

Is this specific to MySQL, because I've used WHERE clauses with COUNT() in
T-SQL... you should be able to specify a WHERE clause using MySQL also. I
believe COUNT() is the same as selecting any other fields. The only
difference is that it doesn't return actual data, only an integer, which
makes it significantly faster if you're working with large result sets.

-----Original Message-----
From: Analysis & Solutions [mailto:danielc at analysisandsolutions.com]
Sent: Tuesday, May 27, 2003 3:16 PM
To: NYPHP Talk
Subject: Re: [nycphp-talk] mySQL optimization question.


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


--- Unsubscribe at http://nyphp.org/list/ ---