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

Question

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?

Answer 1

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

Answer 2

You could just do :

SELECT count(*) FROM tablename;

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

Answer 3

$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)

Answer 4

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.

Answer 5

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

Answer 6

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

Answer 7

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;