which one is faster/smarter and why: COUNT(*) or storing the numbers each the do something

Question

PHP

$total_points = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM account WHERE id='$id'"),0)

Mysql account table

|user_id|

mysql points table

|id | user_id | points |

or

PHP

$total_points = mysql_query("SELECT points FROM account WHERE id='$id'");

Mysql account table

|user_id| points |

Mysql points table

|id | user_id | points |

Answer 1

Storing the variable would probably be faster, but that also means constantly updating it, which can be less efficient / slower in the long run.

Using COUNT(id) will be much better than COUNT(*), however. So my vote would be to use COUNT(id) :)

Answer 2

First off, for the first line you had, I believe it's faster to use FOUND_ROWS() than COUNT(*).

$total_points = mysql_num_rows(mysql_query("SELECT FOUND_ROWS() FROM account WHERE id='$id'"),0)

The second approach would be faster once that points table grows large, but you need to make sure you increment the account table properly so those values are in sync. It could become inaccurate if you forget to add the points in some places, or forget to delete them, etc.

Answer 3

The COUNT(*) or query version should be faster because you are not going futher down to mysql_num_rows. For counting you don't need all fields (*), you should simply do COUNT(fieldID) which is a lot faster.

Few Points To Note:

Note that you are getting only one row anyways because you are using where clause, in other words, the result will be either one row or no row if not found:

$total_points = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM account WHERE id='$id'"),0)

Normally you should count when you are expecting more than one record eg:

$total_points = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM account"),0)

Again, for optimized query, use a single field:

$total_points = mysql_result(mysql_query("SELECT COUNT(fieldID) as Num FROM account"),0)