[ACCEPTED]-Whats the best way to get total # of records in a mysql table with php?-mysql

Accepted answer

Score: 31

You were told correctly. mysql can do this 1 count 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;

Score: 5

You should use SQL's built in COUNT function:

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

Score: 4

MyISAM tables already store the row count

SELECT COUNT(*) FROM table

on a MyISAM table simply reads that value. It 3 doesn't scan the table or the index(es). So, it's 2 just as fast or faster than reading the 1 value from a different table.

Score: 3

According to the MySQL documentation this is most efficient 4 if you're using a MyISAM table (which is 3 the most usual type of tables used):

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

Otherwise 2 you should do as Wayne stated and be sure 1 that the counted column is indexed.

Score: 2

Can I just add, that the most "efficient" way 10 of getting the total number of records, particularly 9 in a large table, is to save the total amount 8 as a number in another table. That way, you 7 don't have to query the entire table everytime 6 you want to get the total.

You will however, have 5 to set up some code or Triggers in the database 4 to increase or decrease that number when 3 a row is added/deleted.

So its not the easiest 2 way, but if your website grows, you should 1 definitely consider doing that.

Score: 2

Even though I agree to use the built-in 4 functions, I don't really see any performance 3 difference between mysql_num_rows and count(id). For 2 25000 results, same performance (can say 1 exact.) Just for the record.

Score: 1

What about something like this:

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

Score: 0

Just wanted to note that SHOW TABLE STATUS returns a Rows column, though 5 I can't speak to its efficiency. Some light 4 Googling turns up reports of slowness in 3 MySQL 4 over two years ago. Might make for 2 interesting time trials.

Also note the InnoDB caveat regarding 1 inaccurate counts.

Score: 0

Use aggregate function. Try the below SQL 1 Command

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

Score: 0

mysqli_query() is deprecated. Better use 2 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 of 1 the db connected to)

Score: 0

I had a large table (>50 million rows) and 3 it took a long time to count the primary 2 key, so I use the following:

SELECT TABLE_NAME, TABLE_ROWS
FROM information_schema.tables
WHERE TABLE_SCHEMA = "database";

Replace database 1 with the name of your schema.

Source: stackoverflow.com

[ACCEPTED]-Whats the best way to get total # of records in a mysql table with php?-mysql

Creating your own TinyURL

What is the best way to bind decimal / double / float values with PDO in PHP?

Why I am getting Cannot pass parameter 2 by reference error when I am using bindParam with a constant value?

warning problem: expects parameter 1 to be mysqli_result

Converting date to this format

How to display Unicode data with PHP

PHP errors -> Warning: mysqli_stmt::execute(): Couldn't fetch mysqli_stmt | Warning: mysqli_stmt::close()

Mysql 1 Random Row

configuring database connection in Yii framework

Typical URL lengths for storage calculation purposes (URL-shortener)