count(*)

Hi all, Is more fast count(*) or count(table.column) ? I have Informix SE-ONLINE 7.23. thanks in advance.

I have no empirical evidence to present, but I would suspect that count(*) is faster. Count(*) counts the ROWS in the table, while count(table.column) would count the number of rows with NON-null values in table.column. That requires an examination of the row elements. Even if the field is not nullable, count(table.column) would not seem to require any less work than count(*). Just my humble, and often foot-in-mouth, opinion Doug Agnew dagnew@charlottepipe.com Gaetano Mendola wrote in message <7fncdv$qfm$1@news.mch.sbs.de>... >Hi all, > >Is more fast count(*) or count(table.column) ? > >I have Informix SE-ONLINE 7.23. > >thanks in advance. > > > > >

"Gaetano Mendola" <mendola@bigfoot.com> writes: > Hi all, > > Is more fast count(*) or count(table.column) ? > > I have Informix SE-ONLINE 7.23. > thanks in advance. Probably "COUNT (1) FROM tablename" Thomas

Is an invalid sintax. Thomas Parsli <thomas.parsli@metamedia.no> wrote in message emlcn215.fsf@metamedia.no... > "Gaetano Mendola" <mendola@bigfoot.com> writes: > > > Hi all, > > > > Is more fast count(*) or count(table.column) ? > > > > I have Informix SE-ONLINE 7.23. > > thanks in advance. > > Probably "COUNT (1) FROM tablename" > > Thomas

"Gaetano Mendola" <mendola@bigfoot.com> writes: > Is an invalid sintax. > Thomas Parsli <thomas.parsli@metamedia.no> wrote in message > emlcn215.fsf@metamedia.no... > > "Gaetano Mendola" <mendola@bigfoot.com> writes: > > > > > Hi all, > > > > > > Is more fast count(*) or count(table.column) ? > > > > > > I have Informix SE-ONLINE 7.23. > > > thanks in advance. > > > > Probably "COUNT (1) FROM tablename" Just add water/SELECT: "SELECT COUNT(1) FROM tablename" Thomas

Obviously!! SELECT COUNT(1) FROM tablename; 201: A syntax error has occurred. Thomas Parsli <thomas.parsli@metamedia.no> wrote in message 90bkn0pz.fsf@metamedia.no... > "Gaetano Mendola" <mendola@bigfoot.com> writes: > > > Is an invalid sintax. > > Thomas Parsli <thomas.parsli@metamedia.no> wrote in message > > emlcn215.fsf@metamedia.no... > > > "Gaetano Mendola" <mendola@bigfoot.com> writes: > > > > > > > Hi all, > > > > > > > > Is more fast count(*) or count(table.column) ? > > > > > > > > I have Informix SE-ONLINE 7.23. > > > > thanks in advance. > > > > > > Probably "COUNT (1) FROM tablename" > > Just add water/SELECT: > "SELECT COUNT(1) FROM tablename" > > Thomas

"Gaetano Mendola" <mendola@bigfoot.com> writes: > Obviously!! > > SELECT COUNT(1) FROM tablename; And tablename beeing the _name_ of the table you would like to count records from? Thomas:D (this is SQL92 as far as I know, so it _should_ work)

Exactly and INFORMIX say: sintax error. Thomas Parsli <thomas.parsli@metamedia.no> wrote in message 7lr4mzqd.fsf@metamedia.no... > "Gaetano Mendola" <mendola@bigfoot.com> writes: > > > Obviously!! > > > > SELECT COUNT(1) FROM tablename;> > And tablename beeing the _name_ of the table you would like to count > records from? > > Thomas:D > (this is SQL92 as far as I know, so it _should_ work)

"Gaetano Mendola" <mendola@bigfoot.com> writes: > Exactly and INFORMIX say: sintax error. > Thomas Parsli <thomas.parsli@metamedia.no> wrote in message > 7lr4mzqd.fsf@metamedia.no... > > "Gaetano Mendola" <mendola@bigfoot.com> writes: > > > > > Obviously!! > > > > > > SELECT COUNT(1) FROM tablename;> > > > And tablename beeing the _name_ of the table you would like to count > > records from? > > > > Thomas:D > > (this is SQL92 as far as I know, so it _should_ work) Ok -looks like Informix can't do... (Sybase can, MySQL can, Oracle can, ...) Thomas

This is a NG for informix. Thomas Parsli <thomas.parsli@metamedia.no> wrote in message 3e1smyq2.fsf@metamedia.no... > "Gaetano Mendola" <mendola@bigfoot.com> writes: > > > Exactly and INFORMIX say: sintax error. > > Thomas Parsli <thomas.parsli@metamedia.no> wrote in message > > 7lr4mzqd.fsf@metamedia.no... > > > "Gaetano Mendola" <mendola@bigfoot.com> writes: > > > > > > > Obviously!! > > > > > > > > SELECT COUNT(1) FROM tablename;> > > > > > And tablename beeing the _name_ of the table you would like to count > > > records from? > > > > > > Thomas:D > > > (this is SQL92 as far as I know, so it _should_ work) > > Ok -looks like Informix can't do... > (Sybase can, MySQL can, Oracle can, ...) > > Thomas

>Is more fast count(*) or count(table.column) ? With count it depends on how accurate you want your result. Count without any restrictive clauses will draw its result from the table statistics, not necessarily correct. To ensure an accurate count one must force a look at the data. WHERE 1=1 works nicely.

Art is correct... When doing a select count(*) from a table, it reads directly from the partition page. This value should always be correct, unless there is some type of corruption or bug. Also, by reading just the partition page, it makes this operation ver quick...... Darin Tracy "Art S. Kagel" wrote: > M00n321 wrote: > > > > >Is more fast count(*) or count(table.column) ? > > > > With count it depends on how accurate you want your result. Count without any > > restrictive clauses will draw its result from the table statistics, not > > necessarily correct. To ensure an accurate count one must force a look at the > > data. WHERE 1=1 works nicely. > > This is just NOT correct. I challenge you to present a case where > COUNT(*) returns anything other than the actual row count! It never > has for me in 18 years! In IDS count(*) with no where clause does > indeed take a count from the table's TABLESPACE TABLESPACE header (not > from systables which row count value is indeed not maintained live) > which is maintained live and is always accurate (OK there have been > occassional bugs that trashed the header, but that aside). Count(*) > with a WHERE clause will use an index if available to count the rows > that pass the filter requirements and if no index is available it > performs a table scan. In any event the results will be the same. > > Art S. Kagel

Art is correct... When doing a select count(*) from a table, it reads directly from the partition page. This value should always be correct, unless there is some type of corruption or bug. Also, by reading just the partition page, it makes this operation ver quick...... Darin Tracy "Art S. Kagel" wrote: > M00n321 wrote: > > > > >Is more fast count(*) or count(table.column) ? > > > > With count it depends on how accurate you want your result. Count without any > > restrictive clauses will draw its result from the table statistics, not > > necessarily correct. To ensure an accurate count one must force a look at the > > data. WHERE 1=1 works nicely. > > This is just NOT correct. I challenge you to present a case where > COUNT(*) returns anything other than the actual row count! It never > has for me in 18 years! In IDS count(*) with no where clause does > indeed take a count from the table's TABLESPACE TABLESPACE header (not > from systables which row count value is indeed not maintained live) > which is maintained live and is always accurate (OK there have been > occassional bugs that trashed the header, but that aside). Count(*) > with a WHERE clause will use an index if available to count the rows > that pass the filter requirements and if no index is available it > performs a table scan. In any event the results will be the same. > > Art S. Kagel

M00n321 wrote: > > >Is more fast count(*) or count(table.column) ? > > With count it depends on how accurate you want your result. Count without any > restrictive clauses will draw its result from the table statistics, not > necessarily correct. To ensure an accurate count one must force a look at the > data. WHERE 1=1 works nicely. This is just NOT correct. I challenge you to present a case where COUNT(*) returns anything other than the actual row count! It never has for me in 18 years! In IDS count(*) with no where clause does indeed take a count from the table's TABLESPACE TABLESPACE header (not from systables which row count value is indeed not maintained live) which is maintained live and is always accurate (OK there have been occassional bugs that trashed the header, but that aside). Count(*) with a WHERE clause will use an index if available to count the rows that pass the filter requirements and if no index is available it performs a table scan. In any event the results will be the same. Art S. Kagel