NYCPHP Meetup

[Hans Zaunere]

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

Hmm, that's a toughy, as it's really DB dependant.  For instance, Oracle
maintains a data dictionary which I believe is where this type of information
is stored (which is cached into memory upon instance startup).  In MySQL I
don't think there is the same notion of a data dictionary, so this type of
info is stored on the filesystem, internal to the table file set itself;
however, since MySQL caches tables that have been opened (not their contents,
just pertinent statistics, ie data dictionary stuff) it'll be just as fast as
Oracle (and certainly much less memory intensive).

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

Hard to say; since there aren't any obvious sys tables, I'd imagine it works
in a similar fashion to MySQL.

Then again, this might be all false since I haven't looked at the data
structures for MySQL  :)

H



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