A faster way to find out the total number of rows in a MySQL database?

Question

If I need to know the total number of rows in a database table, 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 path. I apologize for everything

So, you see that the total amount of data is restored when scanning the entire database.

Is there a better way?

Answer 1

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

This means that you don’t transfer 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 didn’t actually count the number of rows - it would give you 2x the number of columns, since you are counting the number of elements in an array representing one row (and mysql_fetch_array gives you two entries in the array for each column - one numeric and one for the column name)

Answer 2

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

Answer 3

You can simply do:

 SELECT count(*) FROM tablename; 

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

Answer 4

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

Maybe something is missing for me, but if you just want to get the total number of rows in the table, you don't need the WHERE clause. Just do the following:

 SELECT COUNT(*) FROM tablename 

Under the WHERE clause, you will only count the number of rows that satisfy this condition.

Answer 5

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; 

Answer 6

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

Answer 7

If you intend to use the following SQL statement:

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

Make sure you have an index in the link column