The benchmark is showing the difference between the two methods both having a forum_posts table for example and a users table. No pointless tables are being created. We are not storing the total user count because SELECT count(*) from `users` is already stored for myISAM tables as I stated earlier. There are no confusing pieces of code, you're selecting out of the users table already to get the username and other things, what is one more field going to do?d3ad1ysp0rk wrote:It may be faster, but in any real application it would be pointless to have twice the amount of tables for everything.
Having a table `users` that stores data (id,name,pass,etc) and a `users_c` table that stores the amount of users in it seems kinda pointless to me. That's just one more table to deal with, one more query to execute when changing any table, and one more confusing piece of code for new programmers that have never seen the technique before.
There are no more queries being executed using the stored method then the dynamic counting
Where are you getting this table from?a `users_c` table that stores the amount of users
Here's an example to add onto feyd's, which I'm pretty sure I already mentioned but I guess I was not clear-
go here: memberlist.php
Sort by total posts in descending order. Do you really think it's counting every last one of feyd's ~14,000 posts (well it might be I'm not terribly familiar with phpBB but I would guess it is storing the post count. can someone confirm?)
This is a more elegant solution in my opinion, in my benchmark I had 5, or was it 3? ( too lazy to go back and look) users.
If you had 19033 users which this forum does at the time of this post, and 230070 "articles", then listing the top 10 posters would go something like:
select the count from the articles table for each user in turn, this is 19,033 operations examining 230070 rows each time.
Then it sorts on those numbers, storing the post count goes like:
Sort on the post count, that's it.
Also the actual sorting on the post count would be faster because the post count can be an indexed column, whereas an imaginary aliased column such as select count(*) as post_count from table cannot be an index.