What is the best way to get the total number of records in mysql table with php?

Question

What is the most efficient way to select the total number of records from a large table? I'm just doing now

$result = mysql_query("SELECT id FROM table"); $total = mysql_num_rows($result) 

I was told that this is not very efficient or fast if you have many records in the table.

Answer 1

You were told correctly. mysql can do this for you, which is much more efficient.

 $result = mysql_query( "select count(id) as num_rows from table" ); $row = mysql_fetch_object( $result ); $total = $row->num_rows; 

Answer 2

You must use the SQL built in to the COUNT function :

 $result = mysql_query("SELECT COUNT(id) FROM table"); 

Answer 3

MyISAM tables already store row counts

 SELECT COUNT(*) FROM table 

in the MyISAM table just reads this value. It does not scan table or index (s). Thus, it is just as fast or faster than reading a value from another table.

Answer 4

According to the MySQL documentation, this is most effective if you use a MyISAM table (which is the most common type of table used):

 $result = mysql_query("SELECT COUNT(*) FROM table"); 

Otherwise, you must do as Wayne said and make sure that the counted column is indexed.

Answer 5

May I add that the most “efficient” way to get the total number of records, especially in a large table, is to save the total as a number in another table. This way you do not need to query the whole table every time you want to get the total.

However, you need to configure the code or triggers in the database to increase or decrease this number when adding / removing a row.

Thus, this is not the easiest way, but if your site is growing, you should definitely think about it.

Answer 6

Although I agree to use the built-in functions, I see no performance difference between mysql_num_rows and count (id). For 25,000 results, the same performance (can tell for sure.) For recording only.

Answer 7

Something like that:

 $result = mysql_query("SELECT COUNT(id) AS total_things from table"); $row = mysql_fetch_array($result,MYSQL_ASSOC); $num_results = $row["total_things"]; 

Answer 8

I just wanted to notice that SHOW TABLE STATUS returns the Rows column, although I can’t talk about its effectiveness. Some easy Googling shows slowness messages in MySQL 4 more than two years ago. You can do interesting time research.

Also note the InnoDB warning regarding inaccuracies.

Answer 9

Use aggregate function. Try the SQL command below

 $num= mysql_query("SELECT COUNT(id) FROM $table"); 

Answer 10

mysqli_query () is deprecated. Better use this:

 $result = $dbh->query("SELECT id FROM {table_name}"); $total = $result->num_rows; 

Using PDO:

 $result = $dbh->query("SELECT id FROM {table_name}"); $total = $result->rowCount(); 

(where '$ dbh' = handle to the connected db)