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

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

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

I have updated my question. Please see – Starx May 27 '10 at 0:51 1 @Starx The answer's still the same :) – Chris Smith May 27 '10 at 10:01.

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

Of course I'd replace * with one of the column names – Pedro May 26 '10 at 16:59 @Pedro - it might not make that much difference. – ChrisF May 26 '10 at 17:00 3 @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'. – paxdiablo May 26 '10 at 17:07 1 @Pedro: (*) can result in slightly faster performance as MySQL will automatically use an index to get the result. – webbiedave May 26 '10 at 17:09 I stand corrected, and learn something new every day :) – Pedro May 26 '10 at 17:40.

You could just do : SELECT count(*) FROM tablename; for your query. The result will be a single column containing the number of rows.

I have updated my question. Please see – Starx May 27 '10 at 0:53.

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.

1 - helpful note on the WHERE condition. – J. Polfer May 26 '10 at 17:12 I have updated my question.

Please see – Starx May 27 '10 at 0:53.

php.net/manual/en/function.mysql-num-row... You need this I think.

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

– Kenaniah May 26 '10 at 18:10 1 @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.

– Joseph May 26 '10 at 19:20 @Joseph, agreed for other contexts. But in the context of this question, it's a bad idea. – Kenaniah May 26 '10 at 21:07 I have updated my question.

Please see – Starx May 26 '10 at 0:53.

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.

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 $row0; $output$i=mysql_query($sql); } $totalRows=array_sum($ouptput); echo $totalRows.

Atleast write some reason please.. so that I can update my knowledge – diEcho Jun 8 '10 at 19:11.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.