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?

Engadine

Technically, the count($rows) call is counting the rows in the $rows array in PHP-land, not by scanning the database in database-land. – Isolationism

While your method is not optimal, it 's also wrong. You will get number of fields, not rows. Multiplied by 2. – Stockholm

Do you want the link='1'? If all of your records in that table have link='1', then you'll get a count of all the records in that table using the below examples, but that usually isn't the case. If you really want to get all records, I'd remove the link='1'. – Isolationism

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)

Deem

@Engadine The answer's still the same :) – Deem

Answer 2

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

Lorrielorrimer

@Pedro - it might not make that much difference. – Barometrograph

@Pedro et al, you should not use a column name. That doesn't count NULL values in that column. Some people suggest using count(1) because it's faster, but that's rubbish in all but the most brain-dead DBMS'. – Cameroncameroon

@Pedro: (*) can result in slightly faster performance as MySQL will automatically use an index to get the result. – Lorrielorrimer

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.

Isolationism

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.

Residuum

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;

Moke

Answer 6

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

Youthful

-1 Bad idea. This requires that you execute the full query. A SELECT count(*) is worlds more efficient. – Incombustible

@Kenaniah: Not necessarily a bad idea. If all that the OP is looking for is the number of rows, then yes it is a bad idea. However, if the OP needs the data in other manipulations, then mysql_num_rows() might be better suited. – Allurement

@Joseph, agreed for other contexts. But in the context of this question, it's a bad idea. – Incombustible

Answer 7

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

Forgotten