how to count rows in mysql?
Moderator: General Moderators
how to count rows in mysql?
how can i know the total of row in a table by using sql?
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
Code: Select all
<?php
$result = mysql_query("SELECT * FROM table");
echo mysql_num_rows($result);
?>-
magicrobotmonkey
- Forum Regular
- Posts: 888
- Joined: Sun Mar 21, 2004 1:09 pm
- Location: Cambridge, MA
Code: Select all
<?php
$query = "SELECT COUNT(*) FROM tablename";
?>- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
- hydrocomputer
- Forum Newbie
- Posts: 4
- Joined: Mon May 24, 2004 10:22 pm
Don't select everything
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.
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
If you're on MySQL (maybe aplicable to others) you can try:hydrocomputer wrote:select * from table
is not only horrendous for the client, it hits EVERY SINGLE ROW IN THE TABLE.
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