Quickest way to get total records from a mysql table in PHP

Question

Below is the current code I use to get the total number of photos in a DB, An estimated number would even be acceptable as the number of results is just used to determine which folder number I would like to upload into for folder balance of files.

I have a feeling there is a better way then I am using below?

$sql = 'SELECT auto_id FROM friend_user_photo';
$query = executeQuery($sql);
$rowcount = mysql_num_rows($query);

$rowcount returns number of record in the friend_user_photo table

UPDATE:

Isn't there a way to read information about a database. that might be quicker then using above method or COUNT?

Answer 1

SELECT COUNT(auto_id) FROM friend_user_photo

Would be more direct and you wouldn't have to use mysql_num_rows($query). You would just check the value returned from $query.

EDIT: Since you updated that you would like another idea than COUNT.

If auto_id is simply an integer or long, etc... that started at 1 and increments by one then how about LIMIT:

SELECT auto_id FROM friend_user_photo LIMIT 0,1

I'm not sure if it's faster though...

EDIT #2:

According to these posts:

If you are using myisam then COUNT is the fastest:

http://lists.mysql.com/mysql/184095 -

select count(*) from table is the fastest for myisam because it caches row count. This will also include any rows that have null values.

http://stackoverflow.com/questions/116824/whats-the-best-way-to-get-total-of-records-in-a-mysql-table-with-php

Answer 2

You can also use COUNT.

$sql = 'SELECT COUNT(*) FROM friend_user_photo';
$query = executeQuery($sql);

This will return a single row containing the number of results. This is also a regular aggregate function so you can do joins, sub queries, conditions (WHERE), grouping and all that good stuff.

You can also treat this as just another field, so you can alias it and then use it in your joins and sub queries. You can also select additional data, for example:

$sql = 'SELECT COUNT(*), name FROM table GROUP BY name

This will return a distinct list of names, but will also contain the number of records that exist for each of those names.

Edit for alternative to count()

If you don't want to use COUNT(*) you can also use 'show table status'. For example:

$sql = "SHOW TABLE STATUS LIKE 'friend_user_photo'";
// Note, this LIKE is a regular LIKE, so wild cards can be used

This will return a row with various meta-data including Rows, but also including Key Length (in bytes) and Data Length (in bytes) as well as a lot of other data. This is also usually quite fast since it's just pulling meta-data as apposed to running an aggregate function like COUNT(*) (so long as the table isn't locked by another query that's currently running).