Advertisement:

Author Topic: [2.0][2.1] Slow db request for posting activity by time  (Read 662 times)

Offline digger

  • Sr. Member
  • ****
  • Posts: 749
  • Gender: Male
    • realdigger on GitHub
    • SMF Russian Community
[2.0][2.1] Slow db request for posting activity by time
« on: December 12, 2018, 08:23:12 AM »
We have this code in Sources/Profile-View.php
Code: [Select]
// 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']

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 70,187
    • StoryBB/StoryBB on GitHub
Re: [2.0][2.1] Slow db request for posting activity by time
« Reply #1 on: December 12, 2018, 08:25:51 AM »
Not sure it’s entirely normal for 90% of all topics ever posted on the forum to be deleted.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline digger

  • Sr. Member
  • ****
  • Posts: 749
  • Gender: Male
    • realdigger on GitHub
    • SMF Russian Community
Re: [2.0][2.1] Slow db request for posting activity by time
« Reply #2 on: December 12, 2018, 08:41:53 AM »
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.

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 70,187
    • StoryBB/StoryBB on GitHub
Re: [2.0][2.1] Slow db request for posting activity by time
« Reply #3 on: December 12, 2018, 09:09:13 AM »
No, reality won’t always match, but I think it is normally a bit more in line than you’re suggesting.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Online albertlast

  • Development Contributor
  • Full Member
  • *
  • Posts: 456
Re: [2.0][2.1] Slow db request for posting activity by time
« Reply #4 on: December 12, 2018, 02:13:23 PM »
Well i see the problem,
but i came up with a different solution: https://github.com/SimpleMachines/SMF2.1/pull/5197