[2.0][2.1] Slow db request for posting activity by time

Started by digger, December 12, 2018, 08:23:12 AM

Previous topic - Next topic

digger

We have this code in Sources/Profile-View.php
    // Posting activity by time.
    $result = $smcFunc['db_query']('user_activity_by_time', '
        SELECT
            HOUR(FROM_UNIXTIME(poster_time + {int:time_offset})) AS hour,
            COUNT(*) AS post_count
        FROM {db_prefix}messages
        WHERE id_member = {int:current_member}' . ($modSettings['totalMessages'] > 100000 ? '
            AND id_topic > {int:top_ten_thousand_topics}' : '') . '
        GROUP BY hour',
        array(
            'current_member' => $memID,
            'top_ten_thousand_topics' => $modSettings['totalTopics'] - 10000,
            'time_offset' => (($user_info['time_offset'] + $modSettings['time_offset']) * 3600),
        )
    );

But for a big forums $modSettings['totalTopics'] can be much smaller then real last topic id and this request loads too many rows.
For example I have a forum with 60000 topics and last topic id is 597000. When I try to open profile posting activity page of a members with more then 50000 messages it takes 60-100 seconds to show result.
Correct to use here MAX(id_topic) instead $modSettings['totalTopics']

Arantor

Not sure it's entirely normal for 90% of all topics ever posted on the forum to be deleted.

digger

This query was originally built on the wrong assumption that the number of topics always equals last topic id. But real life don't match this.
I looked at some large forums where there is a big difference between the number of topics and last topic id, so when member have many messages it takes some time to show his profile statistics page.

Arantor

No, reality won't always match, but I think it is normally a bit more in line than you're suggesting.

albertlast


Advertisement: