Performance help # 2

[Anon]

This table - Friend contain 99999 records.
This table - totalFriend, store the total
number of friend , i.e 99999

I run 2 SQL scripts to get the total record
with php.

1) Select Count(*) from Friend.

2) Select * from totalFriend.

both also return 99999, but which one run more faster ?

[Anon]

1 will be faster because it returns only the number of records.

2 doesn't return 99999, it returns 99999 records, which you can count using mysql_num_rows().
It's like reading every page on a book to count the pages.

[Anon]

If your table 'totalFriend' contains only a single row, the speed may not differ much--but this ignores the effort required to update 'totalFriend' whenever you insert or delete rows from 'Friend'. Why create a table to duplicate the information that is already available from count(*)?

[Anon]

Woops, ok, my eyes are not quite open yet....

[Anon]

Are you creating a view to get totalfriends? If so then it will be updated automatically, and will always only have one row. It will likely get optimized to very short response times by the built in optimizers of most databases.

If you're running mysql, just use the count(*) as it's plenty fast, doesn't support views, and you would therefore have to update totalfriends every single time you changed the number of records in friends.