Page 2 of 2

Posted: Sun Feb 05, 2006 12:41 am
by josh
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.
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?

There are no more queries being executed using the stored method then the dynamic counting

a `users_c` table that stores the amount of users
Where are you getting this table from?

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.

Posted: Sun Feb 05, 2006 1:13 am
by John Cartwright
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?)
correct.

Posted: Sun Feb 05, 2006 9:34 am
by d3ad1ysp0rk
jshpro2 wrote:Where are you getting this table from?
Your example wasn't 100% straightforward (bad table names, no extra fields to imply what you were talking about) so I got confused and that's where my example came from.

I'm well aware that post counts should be stored in a user table, and I didn't know that anyone actually did it differently.

Posted: Sun Feb 05, 2006 1:35 pm
by s.dot
post counts aren't the only place that stored numbers are good for

what about online messaging systems? the # of messages in the inbox, outbox, sent box, etc. are always fluctuating, which is a lot of calculations to do to get the # correct.

it's also a lot of row counting to count it every time it's loaded

for now I think I'm going to go with row counting until I get my new system developed, and then optimize it. It's kinda hard to do the # count calculations while developing =/

Posted: Sun Feb 05, 2006 8:45 pm
by John Cartwright
I don't think it would be confusing or complex if you design it properly..

This is what somewhat similar to what I've done in the past.. basically allows transparancy of adjusting post counts

Code: Select all

class postAction 
{
   var $inputData;
   var $action;
   var $userId;
   var $validActions = array('addpost', 'editpost', 'deletepost');
   ); 

   function postAction($inputData, $userData) {
      $this->inputData = $inputData;
      $this->action = $this->getAction());
      if ($this->action !== FALSE) {
         if ($this->performAction()) {
            $this->adjustPostCount();
         }
         else {
            trigger_error('postAction "'.$this->action.'" failed', E_USER_WARNING);
         }
      }
      else {
         trigger_error('invalid postAction command', E_USER_WARNING);
      }
   }

   function performAction() {
      switch ($this->action)  {
         case 'addpost' :
            $result = $this->addPost();
            break;
         case 'editPost' :
            $result = $this->editPost();
            break;
         case 'deletePost' :
            $result = $this->deletePost();
            break;
      }
      return $result;
   }
       
   function adjustCountPost() {
       //insert different queries for different actions to adjust post count
   }

   function getAction() {
      if (empty($this->inputData['action']) || !in_array($this->inputData['action'], $this->validActions)) {
         return $this->inputData['action'];
      }
      return false;
   }

   function addPost() { 
      //insert form data to db
      return true;
   }

   function editPost() {
      //edit data from db  
      return true;
   }

   function deletePost() {
      //delete data from db
      return true;
   } 
}

$postAction = new postAction(new httpRequest('POST'), $user->getInfo('id')));