DBI Query rows Benchmark

[Pablo Fischer]

Hello!

I have a questionto those lovers of DBI and Databases: which method is faster
to know the number of rows of a query:

$sth->rows

or

my query but with a COUNT(id) and retrieve the value with:

$query->bind_columns(undef, \$total);

The table its 'big' cause it will manage like 12,000 rows.

In $sth->rows I have this:

SELECT idport FROM ports WHERE port='$portnumber';

in the bind_columns case I have

SELECT COUNT(idport) FROM ports WHERE port='$portnumber'.

I know that I can test it with EXPLAIN in MySql, however Im testing it with
150 records, not with 12,000 (or more) that will be the real size of the
table.

Thanks!
--
Pablo Fischer Sandoval (pablo@pablo.com.mx)
[url]http://www.pablo.com.mx[/url]
[url]http://www.debianmexico.org[/url]
GPG FingerTip: 3D49 4CB8 8951 F2CA 8131 AF7C D1B9 1FB9 6B11 810C
Firma URL: [url]http://www.pablo.com.mx/firmagpg.txt[/url]

[Pablo Fischer]

Thanks!!!
El día Sunday 07 September 2003 7:59 a Bob Showalter mandó el siguiente
correo:

> Pablo Fischer wrote:

> > Hello!
> >
> > I have a questionto those lovers of DBI and Databases: which method
> > is faster to know the number of rows of a query:
> >
> > $sth->rows
> >
> > or
> >
> > my query but with a COUNT(id) and retrieve the value with:
> >
> > $query->bind_columns(undef, \$total);
> >
> > The table its 'big' cause it will manage like 12,000 rows.
> >
> > In $sth->rows I have this:
> >
> > SELECT idport FROM ports WHERE port='$portnumber';
> >
> > in the bind_columns case I have
> >
> > SELECT COUNT(idport) FROM ports WHERE port='$portnumber'.
> >
> > I know that I can test it with EXPLAIN in MySql, however Im testing
> > it with 150 records, not with 12,000 (or more) that will be the real
> > size of the table.

>
> The latter should be faster, because the server can do all the counting and
> doesn't have to pass each row back to the client for counting. If there's
> an index on "port", the count can be determined just by scanning the index,
> which could be even quicker.
>
> In general, you want your queries to return the mininum number of rows
> necessary. The second query will always return one row, so that's your best
> bet.

--
Pablo Fischer Sandoval (pablo@pablo.com.mx)
[url]http://www.pablo.com.mx[/url]
[url]http://www.debianmexico.org[/url]
GPG FingerTip: 3D49 4CB8 8951 F2CA 8131 AF7C D1B9 1FB9 6B11 810C
Firma URL: [url]http://www.pablo.com.mx/firmagpg.txt[/url]

[Bob Showalter]

Pablo Fischer wrote:

> Hello!
>
> I have a questionto those lovers of DBI and Databases: which method
> is faster to know the number of rows of a query:
>
> $sth->rows
>
> or
>
> my query but with a COUNT(id) and retrieve the value with:
>
> $query->bind_columns(undef, \$total);
>
> The table its 'big' cause it will manage like 12,000 rows.
>
> In $sth->rows I have this:
>
> SELECT idport FROM ports WHERE port='$portnumber';
>
> in the bind_columns case I have
>
> SELECT COUNT(idport) FROM ports WHERE port='$portnumber'.
>
> I know that I can test it with EXPLAIN in MySql, however Im testing
> it with 150 records, not with 12,000 (or more) that will be the real
> size of the table.

The latter should be faster, because the server can do all the counting and
doesn't have to pass each row back to the client for counting. If there's an
index on "port", the count can be determined just by scanning the index,
which could be even quicker.

In general, you want your queries to return the mininum number of rows
necessary. The second query will always return one row, so that's your best
bet.

[George Schlossnagle]

On Sunday, September 7, 2003, at 12:33 PM, Pablo Fischer wrote:

> Thanks!!!
> El día Sunday 07 September 2003 7:59 a Bob Showalter mandó el siguiente
> correo:

>> Pablo Fischer wrote:

>>> Hello!
>>>
>>> I have a questionto those lovers of DBI and Databases: which method
>>> is faster to know the number of rows of a query:
>>>
>>> $sth->rows
>>>
>>> or
>>>
>>> my query but with a COUNT(id) and retrieve the value with:

You should also be aware that the rows method does not always return
the number of rows from a select (for example in the oracle oci
libraries it will not always do this, since the size of the return set
is not necessarily known when control is returned to you from
execute()).

George

[Unregistered]

select max(ID) from tableName;