how to count rows in mysql?

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
kevin7
Forum Commoner
Posts: 96
Joined: Fri May 21, 2004 6:54 am

how to count rows in mysql?

Post by kevin7 »

how can i know the total of row in a table by using sql?
duk
Forum Contributor
Posts: 199
Joined: Wed May 19, 2004 8:45 am
Location: London

Post by duk »

i use mysql_affected_rows();
User avatar
mendingo
Forum Commoner
Posts: 28
Joined: Sun May 23, 2004 1:27 pm

Post by mendingo »

I'd use:

Code: Select all

$result = mysql_query("Select * from table");
$numRows = mysql_num_rows($result);
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post by d3ad1ysp0rk »

Code: Select all

<?php
$result = mysql_query("SELECT * FROM table");
echo mysql_num_rows($result);
?>
edit: beat to it :P
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

Code: Select all

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

?>
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
User avatar
hydrocomputer
Forum Newbie
Posts: 4
Joined: Mon May 24, 2004 10:22 pm

Don't select everything

Post 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.
Radical
Forum Newbie
Posts: 3
Joined: Tue May 25, 2004 2:52 am
Location: Bucharest, Romania
Contact:

Re: Don't select everything

Post 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
Post Reply