NYCPHP Meetup

[Sexton, David]

Thanks Hans. I had misread the original post and thought it was declaring
that you couldn't use a WHERE clause with COUNT(). So without specifying a
WHERE clause, I'm assuming it takes predefined values from sys tables on the
disc? Or do these values sit in memory? Reason I'm asking is because MS
Access has no (apparent) sys tables, so I'm wondering if COUNT() works the
same way with Jet.

-----Original Message-----
From: Hans Zaunere [mailto:zaunere at yahoo.com]
Sent: Wednesday, May 28, 2003 10:57 PM
To: NYPHP Talk
Subject: RE: [nycphp-talk] mySQL optimization question.



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