CODEWITHSUNDEEP
phpmysql
user15063
user15063

Reputation:

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

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.

Upvotes: 9

Views: 33991

Answers (11)

kurdtpage
kurdtpage

Reputation: 3221

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

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

Replace database with the name of your schema.

Upvotes: 1

skoob
skoob

Reputation: 1411

MyISAM tables already store the row count

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.

Upvotes: 4

VoxPelli
VoxPelli

Reputation: 2877

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.

Upvotes: 3

Apostolos
Apostolos

Reputation: 3433

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 of the db connected to)

Upvotes: 0

Divakarcool
Divakarcool

Reputation: 481

Use aggregate function. Try the below SQL Command

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

Upvotes: 0

Leon
Leon

Reputation: 89

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

Upvotes: 2

user7675
user7675

Reputation:

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.

Upvotes: 0

Jonathan
Jonathan

Reputation: 2233

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.

Upvotes: 2

Peter Bailey
Peter Bailey

Reputation: 105878

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;

Upvotes: 33

Wayne
Wayne

Reputation: 39878

You should use SQL's built in COUNT function:

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

Upvotes: 6

user15063
user15063

Reputation:

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

Upvotes: 1

Related Questions