So, SELECT count(*)... or mysql_num_rows(...)

[FFMG]

Hi,

I was running a test on a table with 50000 rows.
When I do:

$sql = "SELECT * FROM TABLE";
$result = mysql_query($sql);
$total = mysql_num_rows(result);

I get a 'run out of memory error', (the limit is set low on the test
server).

But when I do:

$sql = "SELECT count(*) as num FROM TABLE";
$result = mysql_query($sql);
$result = mysql_fetch_assoc( $result );
$total = $result['num'];

Everything works fine.

or is
$sql = "SELECT FOUND_ROWS() AS num FROM TABLE";
...
even better?

So What is the 'preferred' method of getting a row count?

FFMG
--

'webmaster forum' (http://www.httppoint.com) | 'webmaster Directory'
(http://www.webhostshunter.com/) | 'Recreation Vehicle insurance'
(http://www.insurance-owl.com/other/car_rec.php)
'Free URL redirection service' (http://urlkick.com/)
------------------------------------------------------------------------
FFMG's Profile: http://www.httppoint.com/member.php?userid=580
View this thread: http://www.httppoint.com/showthread.php?t=18782

Message Posted via the webmaster forum http://www.httppoint.com, (Ad revenue sharing).

[ZeldorBlat]

On Jul 26, 2:14 am, FFMG <[e-mail]:

Hi,

I was running a test on a table with 50000 rows.
When I do:

$sql = "SELECT * FROM TABLE";
$result = mysql_query($sql);
$total = mysql_num_rows(result);

I get a 'run out of memory error', (the limit is set low on the test
server).

But when I do:

$sql = "SELECT count(*) as num FROM TABLE";
$result = mysql_query($sql);
$result = mysql_fetch_assoc( $result );
$total = $result['num'];

Everything works fine.

or is
$sql = "SELECT FOUND_ROWS() AS num FROM TABLE";
..
even better?

So What is the 'preferred' method of getting a row count?

FFMG

--

'webmaster forum' (http://www.httppoint.com) | 'webmaster Directory'
(http://www.webhostshunter.com/) | 'Recreation Vehicle insurance'
(http://www.insurance-owl.com/other/car_rec.php)
'Free URL redirection service' (http://urlkick.com/)
------------------------------------------------------------------------
FFMG's Profile:http://www.httppoint.com/member.php?userid=580
View this thread:http://www.httppoint.com/showthread.php?t=18782

Message Posted via the webmaster forumhttp://www.httppoint.com, (Ad revenue sharing).

Always use "select count(*)" for this type of thing.

The database doesn't necessarily need to go and actually get all the
rows to figure it out this way. If you "select *" and then ask for
the row count he actually needs to go get all those rows, which is why
you're running out of memory.

[Rik]

On Thu, 26 Jul 2007 08:14:09 +0200, FFMG <FF*********@no-mx.httppoint.com
wrote:

>
Hi,

I was running a test on a table with 50000 rows.
When I do:

$sql = "SELECT * FROM TABLE";
$result = mysql_query($sql);
$total = mysql_num_rows(result);

I get a 'run out of memory error', (the limit is set low on the test
server).

But when I do:

$sql = "SELECT count(*) as num FROM TABLE";
$result = mysql_query($sql);
$result = mysql_fetch_assoc( $result );
$total = $result['num'];

Everything works fine.

or is
$sql = "SELECT FOUND_ROWS() AS num FROM TABLE";
..
even better?

So What is the 'preferred' method of getting a row count?

If you don't need the rows themselves, keep use of resources at a low by
using a COUNT() construct. If you do need all the rows, use
mysql_num_rows(), underneath the surface it is essentially the same as
FOUND_ROWS(), with the added bonus of getting an integer straight back.
--
Rik Wasmus

[Captain Paralytic]

On 26 Jul, 14:20, Rik <[e-mail]:
| mysql_num_rows(), underneath the surface it is essentially the same
as
| FOUND_ROWS(), with the added bonus of getting an integer straight
back.

I don't think so Rik!

mysql_num_rows() will tell you how many rows have been returned in
this particular request.

FOUND_ROWS() will tell you how many rows would have been returned if
you did not have a LIMIT clause on the previous query and it also
requires the previous query to have had SQL_CALC_FOUND_ROWS in it.

SELECT FOUND_ROWS() AS num FROM TABLE

will return you the value of 0 for as many rows as there are in the
table.

[Webrickco]

On Jul 26, 3:03 pm, Captain Paralytic <[e-mail]:

On 26 Jul, 14:20, Rik <[e-mail]:
| mysql_num_rows(), underneath the surface it is essentially the same
as
| FOUND_ROWS(), with the added bonus of getting an integer straight
back.

I don't think so Rik!

mysql_num_rows() will tell you how many rows have been returned in
this particular request.

FOUND_ROWS() will tell you how many rows would have been returned if
you did not have a LIMIT clause on the previous query and it also
requires the previous query to have had SQL_CALC_FOUND_ROWS in it.

SELECT FOUND_ROWS() AS num FROM TABLE

will return you the value of 0 for as many rows as there are in the
table.

For compatibility purposes use something standard like select count.

[Captain Paralytic]

On 27 Jul, 14:24, Webrickco <[e-mail]:

On Jul 26, 3:03 pm, Captain Paralytic <[e-mail]:

On 26 Jul, 14:20, Rik <[e-mail]:
| mysql_num_rows(), underneath the surface it is essentially the same
as
| FOUND_ROWS(), with the added bonus of getting an integer straight
back.

I don't think so Rik!

mysql_num_rows() will tell you how many rows have been returned in
this particular request.

FOUND_ROWS() will tell you how many rows would have been returned if
you did not have a LIMIT clause on the previous query and it also
requires the previous query to have had SQL_CALC_FOUND_ROWS in it.

SELECT FOUND_ROWS() AS num FROM TABLE

will return you the value of 0 for as many rows as there are in the
table.

| For compatibility purposes use something standard like select count.

What has this got to do with my post?