Page 1 of 1

how to count rows in mysql?

Posted: Mon May 24, 2004 7:01 am
by kevin7
how can i know the total of row in a table by using sql?

Posted: Mon May 24, 2004 7:09 am
by duk
i use mysql_affected_rows();

Posted: Mon May 24, 2004 7:15 am
by mendingo
I'd use:

Code: Select all

$result = mysql_query("Select * from table");
$numRows = mysql_num_rows($result);

Posted: Mon May 24, 2004 7:15 am
by d3ad1ysp0rk

Code: Select all

<?php
$result = mysql_query("SELECT * FROM table");
echo mysql_num_rows($result);
?>
edit: beat to it :P

Posted: Mon May 24, 2004 2:10 pm
by magicrobotmonkey

Code: Select all

<?php
$query = "SELECT COUNT(*) FROM tablename";

?>

Posted: Mon May 24, 2004 3:41 pm
by John Cartwright
duk wrote:i use mysql_affected_rows();
Thats bad practice, uses a lot of CPU that you shouldn't be and don't ahve to.

Don't select everything

Posted: Mon May 24, 2004 10:22 pm
by hydrocomputer
select * from table

is not only horrendous for the client, it hits EVERY SINGLE ROW IN THE TABLE.

select count(*) from table

is the only clean way to go, and incidentally it works on every SQL, not just MYsql.

If you tried some of the other variants on a 1-million row table, you'd have serious problems.

Re: Don't select everything

Posted: Tue May 25, 2004 2:52 am
by Radical
hydrocomputer wrote:select * from table
is not only horrendous for the client, it hits EVERY SINGLE ROW IN THE TABLE.
If you're on MySQL (maybe aplicable to others) you can try:
SHOW TABLE STATUS LIKE `tablename`

As an answer you'll get a lot of columns:
Name, Type, Row_format, Rows, Avg_row_length, Data_length, Max_data_length, Index_length, Data_free, Auto_increment, Create_time, Update_time, Check_time, Create_options, Comment.

You are interested in: Rows.

I do not remember, though, if SHOW TABLE STATUS opens the table... if it does there would be problems with big tables (over 1 milion entries...) and only if "Row_format" is DYNAMIC... cuz' FIXED tables are very fast. If it only opens the table definition file (as in "SHOW COLUMNS FROM `tablename`") then you'll see:
0 rows affected; 15 field(s); 1 record(s); Time: 0,00 sec
Preety neat... the part with Time: 0,00 sec