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($sql, $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_unbuffered_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]

On Mon, 28 Jul 2003 14:22:40 GMT, "Xizor" <no**@nope.com> wrote:

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($sql, $db);
$num = mysql_num_rows($result);
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.
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_unbuffered_query() would solve this, would it?
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.
Basically my question is this: what is the fastest, most effecient way to
just find out the number of rows in a table?

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 (an**@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]

use

select count(*) from blah;

It will return just the number.
"Xizor" <no**@nope.com> wrote in message
news:QkaVa.147137$GL4.37783@rwcrnsc53...

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($sql, $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_unbuffered_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.

[Craig Bailey]

In article <%Z********************@newssrv26.news.prodigy.com >,
"s a n j a y" <so*****@somewhere.com> wrote:

use

select count(*) from blah;

It will return just the number.

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) - "Broadcasting 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.
cc*@floydianslip.com - AIM: RandomPrec - www.floydianslip.com