Fork me on GitHub
Subscribe 5

Ticket #299 (fixed enhancement)

Cache board statistics

  • Created: 2011-02-19 16:59:17
  • Reported by: adaur
  • Assigned to: Reines
  • Milestone: 1.4.5
  • Component: code
  • Priority: normal

In order to reduce the number of queries done by FluxBB on the index, adding a "Board information" feature would be very useful, by displaying or not

Newest registered user
Total number of registered user
Total number of topic
Total number of posts

0.00058     SELECT COUNT(id)-1 FROM users WHERE group_id!=0
0.00043     SELECT id, username FROM users WHERE group_id!=0 ORDER BY registered DESC LIMIT 1
0.00034     SELECT SUM(num_topics), SUM(num_posts) FROM forums

History

Visman 2011-02-20 01:07:32

It is possible to use a cache for some the data as it is done by me.

...
// Collect some statistics from the database
// MOD кэш пользователей - Visman
if (file_exists(FORUM_CACHE_DIR.'cache_users.php'))
    include FORUM_CACHE_DIR.'cache_users.php';

if (!defined('PUN_USERS_LOADED'))
{
    if (!defined('FORUM_CACHE_FUNCTIONS_LOADED'))
        require PUN_ROOT.'include/cache.php';

    generate_users_cache();
    require FORUM_CACHE_DIR.'cache_users.php';
}

$result = $db->query('SELECT SUM(num_topics), SUM(num_posts) FROM '.$db->prefix.'forums') or error('Unable to fetch topic/post count', __FILE__, __LINE__, $db->error());
list($stats['total_topics'], $stats['total_posts']) = $db->fetch_row($result);

if ($pun_user['g_view_users'] == '1')
    $stats['newest_user'] = '<a href="profile.php?id='.$stats['last_user']['id'].'">'.pun_htmlspecialchars($stats['last_user']['username']).'</a>';
else
    $stats['newest_user'] = pun_htmlspecialchars($stats['last_user']['username']);

if (!empty($forum_actions))
...

-2 querys wink

adaur 2011-02-20 10:56:16

Ok, when do you call this function? After each post?

Reines 2011-02-20 11:43:06

  • Uploaded patch fluxbb-userinfocache.patch. (view)

I've created a patch to cache this information in the same way Visman does. Visman, where do you regenerate the cache, have I missed anywhere?

  • On user login if their group is PUN_UNVERIFIED.

  • On user registration.

  • On user group change in profile.

Reines 2011-02-20 11:43:37

  • Milestone set to 1.4.5.
  • Owner set to Reines.

Visman 2011-02-20 12:51:45

I did not regenerate the cache in file extern.php.
I did regenerate the cache in file register.php after

        // Must the user verify the registration or do we log him/her in right now?
        if ($pun_config['o_regs_verify'] == '1')
        {
            // Load the "welcome" template
            $mail_tpl = trim(file_get_contents(PUN_ROOT.'lang/'.$pun_user['language'].'/mail_templates/welcome.tpl'));

            // The first row contains the subject
            $first_crlf = strpos($mail_tpl, "\n");
            $mail_subject = trim(substr($mail_tpl, 8, $first_crlf-8));
            $mail_message = trim(substr($mail_tpl, $first_crlf));

            $mail_subject = str_replace('<board_title>', $pun_config['o_board_title'], $mail_subject);
            $mail_message = str_replace('<base_url>', get_base_url().'/', $mail_message);
            $mail_message = str_replace('<username>', $username, $mail_message);
            $mail_message = str_replace('<password>', $password1, $mail_message);
            $mail_message = str_replace('<login_url>', get_base_url().'/login.php', $mail_message);
            $mail_message = str_replace('<board_mailer>', $pun_config['o_board_title'].' '.$lang_common['Mailer'], $mail_message);

            pun_mail($email1, $mail_subject, $mail_message);

            message($lang_register['Reg email'].' <a href="mailto:'.$pun_config['o_admin_email'].'">'.$pun_config['o_admin_email'].'</a>.', true);
        }

I did regenerate the cache in file profile.php when removing the user.

Visman 2011-02-20 12:54:02

In login.php similarly your variant.

Reines 2011-02-20 12:58:26

Cheers, commit. Adaur would you still push for having these stats optional, or is this caching enough?

adaur 2011-02-20 13:09:23

I don't know, what do you think? An option is not that heavy, anyway caching these stats is a very good idea smile

Visman 2011-02-20 13:24:39

to Reines:
Pay attention to file register.php!
In your variant early regenerate the cache.

Reines 2011-02-20 13:32:45

What's wrong with regenerating it where I have? All that's done after that is sending a couple of emails then setting the cookie - none of that will have any effect on the cache?

Visman 2011-02-20 14:11:59

If Verify registrations = Yes, then early.

Reines 2011-02-20 14:19:21

Ah I see - it isn't a case of it being wrong, it's just a case of it not being as efficient as it could be - cheers, moved.

@Adaur: I'd probably vote against adding an option now, though mainly because too many options gets confusing and now it wont have much effect anyway.

adaur 2011-02-20 14:23:14

Ok, in this case, the ticket is fixed.

Franz 2011-02-20 15:00:44

  • Summary changed from Add a "Board information" feature to Cache board statistics.

Reines 2011-02-20 15:43:14

  • Status changed from open to fixed.

quy 2011-02-21 04:15:33

Total number of topic
Total number of posts

A typical board is probably not active with postings all day long. Would it be feasible to cache this too in a separate cache file?

Reines 2011-02-21 08:58:35

It would totally depend on how busy the board is, and my thinking would go - if the board is really busy then there is no benefit - it might even make the situation worse. If the board is quiet, then the load will be low anyway so the need for cache is low.

Visman 2011-03-20 11:03:37

profile.php

        // Delete user avatar
        delete_avatar($id);

        redirect('index.php', $lang_profile['User delete redirect']);

-->

        // Delete user avatar
        delete_avatar($id);

        // Regenerate the users info cache
        if (!defined('FORUM_CACHE_FUNCTIONS_LOADED'))
            require PUN_ROOT.'include/cache.php';

        generate_users_info_cache();

        redirect('index.php', $lang_profile['User delete redirect']);