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']
Not sure it's entirely normal for 90% of all topics ever posted on the forum to be deleted.
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.
No, reality won't always match, but I think it is normally a bit more in line than you're suggesting.
Well i see the problem,
but i came up with a different solution: https://github.com/SimpleMachines/SMF2.1/pull/5197