MySQL: Number of Rows Question

[Xizor]

Ok, what I want to do is find out the number of rows in a table. The most
obvious solution is to do something like the following:

$sql = "SELECT blah FROM blah WHERE 1";
$result = mysql_query($sq l, $db);
$num = mysql_num_rows( $result);

If you have a large table, to me that seems like it would be a system hog if
all you want is the number of rows but not the data. I'm not sure if using
mysql_unbuffere d_query() would solve this, would it?

Basically my question is this: what is the fastest, most effecient way to
just find out the number of rows in a table?

Thank you.

[Andy Hassall]

Re: MySQL: Number of Rows Question

On Mon, 28 Jul 2003 14:22:40 GMT, "Xizor" <nope@nope.co m> wrote:
[color=blue]
>Ok, what I want to do is find out the number of rows in a table. The most
>obvious solution is to do something like the following:
>
>$sql = "SELECT blah FROM blah WHERE 1";
>$result = mysql_query($sq l, $db);
>$num = mysql_num_rows( $result);[/color]

That is the worst possible way, fetching the entire contents of the
table off disk, into memory, transferred over to the client, and then
ignored.
[color=blue]
>If you have a large table, to me that seems like it would be a system hog if
>all you want is the number of rows but not the data. I'm not sure if using
>mysql_unbuffer ed_query() would solve this, would it?[/color]

No, because mysql_num_rows becomes unavailable, as the database
doesn't know how many rows are in the result set - it starts
transferring them to the client before they've all been read out of
the database.
[color=blue]
>Basically my question is this: what is the fastest, most effecient way to
>just find out the number of rows in a table?[/color]

If you want to count the number of rows, use COUNT, which returns a
single row with the number of rows matched.

MySQL keeps total number of rows stored in a table as part of the
metadata for a table, so 'select count(*) from tab' doesn't even
access the table.

And if you have a WHERE clause, then you avoid fetching all the data
that you don't want if you use COUNT; the database just counts the
number of matching rows without reading the data itself.

--
Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[s a n j a y]

Re: MySQL: Number of Rows Question

use

select count(*) from blah;

It will return just the number.


"Xizor" <nope@nope.co m> wrote in message
news:QkaVa.1471 37$GL4.37783@rw crnsc53...[color=blue]
> Ok, what I want to do is find out the number of rows in a table. The most
> obvious solution is to do something like the following:
>
> $sql = "SELECT blah FROM blah WHERE 1";
> $result = mysql_query($sq l, $db);
> $num = mysql_num_rows( $result);
>
> If you have a large table, to me that seems like it would be a system hog[/color]
if[color=blue]
> all you want is the number of rows but not the data. I'm not sure if using
> mysql_unbuffere d_query() would solve this, would it?
>
> Basically my question is this: what is the fastest, most effecient way to
> just find out the number of rows in a table?
>
> Thank you.
>
>[/color]

[Craig Bailey]

Re: MySQL: Number of Rows Question

In article <%ZaVa.27493$BM .8923438@newssr v26.news.prodig y.com>,
"s a n j a y" <someone@somewh ere.com> wrote:
[color=blue]
> use
>
> select count(*) from blah;
>
> It will return just the number.[/color]

FYI, if the original poster has learned SQL mostly through PHP books,
etc., he might wanna look into some SQL-specific books. I did, and was
surprised at how many things one can do with the SQL query itself (like
the above!).

--
Floydian Slip(tm) - "Broadcasti ng from the dark side of the moon"
Random Precision Productions(tm)
67 Union St. #2D, Winooski, Vt. 05404-1948 USA
Sundays, 7-8 pm - Champ 101.3 FM, Colchester; 102.1 FM, Randolph, Vt.
ccb@floydiansli p.com - AIM: RandomPrec - www.floydianslip.com