CODEWITHSUNDEEP
phpmysqlrecords
Starx
Starx

Reputation: 78971

Faster way to know the total number of rows in MySQL database?

If I need to know the total number of rows in a table of database I do something like this:

$query = "SELECT * FROM tablename WHERE link='1';";
$result = mysql_query($query);
$count = mysql_num_rows($result);

Updated: I made a mistake, above is my actual way. I apologize to all

So you see the total number of data is recovered scanning through the entire database.

Is there a better way?

Upvotes: 6

Views: 3136

Answers (7)

xkeshav
xkeshav

Reputation: 54016

use below code

$qry=SHOW TABLES FROM 'database_name';
$res=mysql_query($qry);
$output=array();
$i=0;
while($row=mysql_fetch_array($res,MYSQL_NUM)){
       ++$i;
       $sql=SELECT COUNT(*) FROM $row[0];
       $output[$i]=mysql_query($sql);
 }
$totalRows=array_sum($ouptput);
echo $totalRows;

Upvotes: 1

invarbrass
invarbrass

Reputation: 2103

If you are going to use the following SQL statement:

SELECT COUNT(*) FROM tablename WHERE link='1';

Make sure you have an index on the 'link' column

Upvotes: 0

Arshdeep
Arshdeep

Reputation: 4323

http://php.net/manual/en/function.mysql-num-rows.php You need this i think.

Upvotes: 0

J. Polfer
J. Polfer

Reputation: 12481

You could just do :

SELECT count(*) FROM tablename;

for your query. The result will be a single column containing the number of rows.

Upvotes: 4

Mark Byers
Mark Byers

Reputation: 838076

If I need to know the total number of rows in a table of database

Maybe I'm missing something here but if you just want to get the total number of rows in a table you don't need a WHERE condition. Just do this:

SELECT COUNT(*) FROM tablename

With the WHERE condition you will only be counting the number of rows that meet this condition.

Upvotes: 3

Chris
Chris

Reputation: 10435

$query = "SELECT COUNT(*) FROM tablename WHERE link = '1'";
$result = mysql_query($query);
$count = mysql_result($result, 0);

This means you aren't transferring all your data between the database and PHP, which is obviously a huge waste of time and resources.

For what it's worth, your code wouldn't actually count the number of rows - it'd give you 2x the number of columns, as you're counting the number of items in an array representing a single row (and mysql_fetch_array gives you two entries in the array per column - one numerical and one for the column name)

Upvotes: 18

webbiedave
webbiedave

Reputation: 48897

SELECT COUNT(*) FROM tablename WHERE link='1';

Upvotes: 10

Related Questions