quickly get number of rows in a table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Sevengraff
Forum Contributor
Posts: 232
Joined: Thu Apr 25, 2002 9:34 pm
Location: California USA
Contact:

quickly get number of rows in a table

Post by Sevengraff »

I looked in the php manual, and there didn't seem to be a function for this.
The way I do it is to have a query that will run throught all the rows
$sql = "SELECT * FROM USERS";
and then use mysql_affected_rows as my count.
but this seems like an inefficent way of doing it. how should i do it?
nieve
Forum Newbie
Posts: 11
Joined: Tue Dec 03, 2002 10:26 pm

Post by nieve »

mysql_num_rows?

$query = "SELECT * FROM users";
$query1 = mysql_query($query);
$q_r = mysql_num_rows($query1);
echo "$q_r";
User avatar
Sevengraff
Forum Contributor
Posts: 232
Joined: Thu Apr 25, 2002 9:34 pm
Location: California USA
Contact:

Post by Sevengraff »

Is that way any better?
hedge
Forum Contributor
Posts: 234
Joined: Fri Aug 30, 2002 10:19 am
Location: Calgary, AB, Canada

Post by hedge »

select count(*) from tablename
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post by Rob the R »

You can improve performance slightly if you know of a column in your table that has a value (is non-null) for every row. Then you can use:

Code: Select all

select count(column_name) from tablename
User avatar
Sevengraff
Forum Contributor
Posts: 232
Joined: Thu Apr 25, 2002 9:34 pm
Location: California USA
Contact:

Post by Sevengraff »

iv'e never used count() before, so i'll read up on it and just keep doing what i do for now. Thanks for the replies.
User avatar
puckeye
Forum Contributor
Posts: 105
Joined: Fri Dec 06, 2002 7:26 pm
Location: Joliette, QC, CA
Contact:

COUNT() function revisited

Post by puckeye »

HI,

Is there a quick way to count the total rows that would be returned when LIMIT is used.

I need to do something like XX out of XXX records.

Code: Select all

SELECT * FROM TABLE WHERE X = Y LIMIT 0, 10
Taking into account that there is more then 10 records meeting the X = Y condition how would I know how many actually met the condition without having to call a second query like

Code: Select all

SELECT * FROM TABLE WHERE X = Y
Thanks for help
User avatar
Sevengraff
Forum Contributor
Posts: 232
Joined: Thu Apr 25, 2002 9:34 pm
Location: California USA
Contact:

Post by Sevengraff »

i think you could just use mysql_affected_rows.

Code: Select all

$sql = "SELECT * FROM TABLE WHERE X = Y LIMIT 0, 10";
$runsql = mysql_query($sql);
$returned_rows=mysql_affected_rows();
User avatar
puckeye
Forum Contributor
Posts: 105
Joined: Fri Dec 06, 2002 7:26 pm
Location: Joliette, QC, CA
Contact:

Post by puckeye »

It took a long time for me to get back to this thread... LOL

mysql_affected_rows(); can only be used to count the number of INSERT, UPDATE and DELETE not SELECT.

Thanks though
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

select count(*) from table where X=Y

Much more efficient than select * and checking numrows.

With select count(*) you just return an integer, with select (*) you return
the entire table - unnecessary network traffic with that!

Phil J.
User avatar
puckeye
Forum Contributor
Posts: 105
Joined: Fri Dec 06, 2002 7:26 pm
Location: Joliette, QC, CA
Contact:

Post by puckeye »

fractalvibes wrote:select count(*) from table where X=Y

Much more efficient than select * and checking numrows.

With select count(*) you just return an integer, with select (*) you return
the entire table - unnecessary network traffic with that!

Phil J.
But that means a second query.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

If you are just wanting a count, the one query should suffice.
Post Reply