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

Question

Whats the most efficient way of selecting total number of records from a large table? Currently, Im simply doing

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

I was told this was not very efficient or fast, if you have a lot of records in the table.

Answer 1

You should use SQL's built in COUNT function:

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

Answer 2

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

Answer 3

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"];

Answer 4

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

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

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

Answer 5

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

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

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

Answer 6

MyISAM tables already store the row count, so "SELECT COUNT(*) FROM table" on a MyISAM table simply reads that value. It doesn't scan the table or the index(es). So, it's just as fast or faster than reading the value from a different table.

Answer 7

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

Also note the InnoDB caveat regarding inaccurate counts.