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

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

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

Answer 3

You could just do :

SELECT count(*) FROM tablename;

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