NYCPHP Meetup

[Hans Zaunere]

--- "Sexton, David" <David.SextonJr at ubspw.com> wrote:
> >>>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.

As others have mentioned, doing the COUNT in the DB (MySQL or any other) is
*much* faster, for the number of reasons mentioned.

COUNT uses internal database structures to do the calculations, making it the
fastest possible method.  Using WHERE or GROUP BY obviously would entail a
bit more work because it needs to be selective on the count, and can't simply
look at only internal table structures, but will remain the fastest method
for making a count in any DB.

H




> 
> -----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/ ---
> 
>