Top Contributors of the Month v2.1.4
- Author Wutime
- Creation date
Install Instructions
Performance Recommendations
Performance recommendations are geared towards large forums.
For small forums the addon will be performant out of the box assuming caching is enabled. I do recommend you install the Recommended Scoring Metric indexes.
For medium to large forums you'll want to:
Scoring Metric Implications (why caching is necessary)
The following are estimates based on active users and the time required to to "rebuild" the scoring for all users. Rebuilds are only required once per cache cycle. The most resource-intensive metrics, list by rebuild effort, are listed below.
1) Thread Replies Received (Most Expensive)
Why: This metric (reply_sub) is the costliest because it requires a JOIN between xf_thread and xf_post to count posts in threads where the thread’s user_id differs from the post’s user_id (p.user_id != t.user_id). This involves scanning xf_post (1–10M rows) and xf_thread (100K–1M rows) with multiple conditions (post_date, message_state, discussion_state). The GROUP BY t.user_id and date range filters add significant overhead.
Estimated Cost: For 1,000 active users, this could take 0.8–1.5 seconds alone due to the large join and filtering. For 10,000 active users, it scales poorly (2–5 seconds).
2) Reaction Score Received (Most Expensive)
Why: This metric (r and rx) joins xf_reaction_content (1–10M rows) with xf_reaction to sum reaction_score for each user’s content (content_user_id). The GROUP BY u.user_id and date range filter (reaction_date) make it heavy, especially if xf_reaction_content is large. The additional reaction_score > 0 condition adds minor overhead.
Estimated Cost: 0.5–1.2 seconds for 1,000 active users, 1.5–3 seconds for 10,000 users.
3) Reactions Given (Moderate)
Why: This metric (reaction_given_sub) counts reactions in xf_reaction_content where reaction_user_id = u.user_id. It’s similar to reaction score but simpler, as it uses COUNT instead of SUM and doesn’t join xf_reaction. However, xf_reaction_content’s size makes it expensive, and the date range filter adds overhead.
Estimated Cost: 0.4–1.0 seconds for 1,000 active users, 1.2–2.5 seconds for 10,000 users.
4) Post Count (Less Expensive)
Why: This metric (post_sub) counts posts in xf_post (1–10M rows) where message_state = 'visible' and joins xf_thread to check discussion_state and node_id. Despite xf_post’s size, the query is relatively straightforward, with a single COUNT and GROUP BY user_id. Proper indexing can make this efficient.
Estimated Cost: 0.3–0.6 seconds for 1,000 active users, 0.8–1.5 seconds for 10,000 users.
5) Thread Count (Less Expensive - Smaller Row Size)
Why: This metric (thread_sub) counts threads in xf_thread (100K–1M rows) where discussion_state = 'visible. It’s simpler than post count because xf_thread is smaller, and there’s no join with xf_post. The date range and node_id filters are straightforward.
Estimated Cost: 0.2–0.4 seconds for 1,000 active users, 0.5–1.0 seconds for 10,000 users.
6) Milestone/Trophy Points (Least Expensive)
Why: This metric (milestone_sub) sums trophy_points from xf_user_trophy (10K–100K rows) joined with xf_trophy. The table is much smaller than xf_post or xf_reaction_content, and the GROUP BY user_id is fast. The date range and trophy_id NOT IN (31, 32, 33) filters are lightweight.
Estimated Cost: Insignificant
Scoring Metric Indexes (recommended)
Applying indexes listed below may increase database size by ~3–6%, but will significantly boost query efficiency by reducing execution times up to 80%, making it a worthwhile investment for faster leaderboards, especially on busy forums. I would recommend only implementing the "High Priority" indexes.
The indexes with the biggest benefits (if using that metric) are:
- Uninstall previous TPM addon (No worries, you won't lost any data)
- Install this add-on
- Done!
- Installer automatically enables permission to view widget for "unregistered users" & "registered users"
- Installer automatically enables permission to change the date (view historical data) for "registered users"
- Settings: admin.php?options/groups/wutc/
- Usergroup permissions: /admin.php?permissions/user-groups/
- Widget: admin.php?widgets/
- Styles: admin.php?style-properties/
Performance Recommendations
Performance recommendations are geared towards large forums.
For small forums the addon will be performant out of the box assuming caching is enabled. I do recommend you install the Recommended Scoring Metric indexes.
For medium to large forums you'll want to:
- Choose lengthy cache durations
- Only select scoring metrics you actually need
- Implement the recommended "high priority" indexes for your selected scoring metrics (info below)
- Do not flush caches often (set it and forget it)
Scoring Metric Implications (why caching is necessary)
The following are estimates based on active users and the time required to to "rebuild" the scoring for all users. Rebuilds are only required once per cache cycle. The most resource-intensive metrics, list by rebuild effort, are listed below.
1) Thread Replies Received (Most Expensive)
Why: This metric (reply_sub) is the costliest because it requires a JOIN between xf_thread and xf_post to count posts in threads where the thread’s user_id differs from the post’s user_id (p.user_id != t.user_id). This involves scanning xf_post (1–10M rows) and xf_thread (100K–1M rows) with multiple conditions (post_date, message_state, discussion_state). The GROUP BY t.user_id and date range filters add significant overhead.
Estimated Cost: For 1,000 active users, this could take 0.8–1.5 seconds alone due to the large join and filtering. For 10,000 active users, it scales poorly (2–5 seconds).
2) Reaction Score Received (Most Expensive)
Why: This metric (r and rx) joins xf_reaction_content (1–10M rows) with xf_reaction to sum reaction_score for each user’s content (content_user_id). The GROUP BY u.user_id and date range filter (reaction_date) make it heavy, especially if xf_reaction_content is large. The additional reaction_score > 0 condition adds minor overhead.
Estimated Cost: 0.5–1.2 seconds for 1,000 active users, 1.5–3 seconds for 10,000 users.
3) Reactions Given (Moderate)
Why: This metric (reaction_given_sub) counts reactions in xf_reaction_content where reaction_user_id = u.user_id. It’s similar to reaction score but simpler, as it uses COUNT instead of SUM and doesn’t join xf_reaction. However, xf_reaction_content’s size makes it expensive, and the date range filter adds overhead.
Estimated Cost: 0.4–1.0 seconds for 1,000 active users, 1.2–2.5 seconds for 10,000 users.
4) Post Count (Less Expensive)
Why: This metric (post_sub) counts posts in xf_post (1–10M rows) where message_state = 'visible' and joins xf_thread to check discussion_state and node_id. Despite xf_post’s size, the query is relatively straightforward, with a single COUNT and GROUP BY user_id. Proper indexing can make this efficient.
Estimated Cost: 0.3–0.6 seconds for 1,000 active users, 0.8–1.5 seconds for 10,000 users.
5) Thread Count (Less Expensive - Smaller Row Size)
Why: This metric (thread_sub) counts threads in xf_thread (100K–1M rows) where discussion_state = 'visible. It’s simpler than post count because xf_thread is smaller, and there’s no join with xf_post. The date range and node_id filters are straightforward.
Estimated Cost: 0.2–0.4 seconds for 1,000 active users, 0.5–1.0 seconds for 10,000 users.
6) Milestone/Trophy Points (Least Expensive)
Why: This metric (milestone_sub) sums trophy_points from xf_user_trophy (10K–100K rows) joined with xf_trophy. The table is much smaller than xf_post or xf_reaction_content, and the GROUP BY user_id is fast. The date range and trophy_id NOT IN (31, 32, 33) filters are lightweight.
Estimated Cost: Insignificant
Scoring Metric Indexes (recommended)
Applying indexes listed below may increase database size by ~3–6%, but will significantly boost query efficiency by reducing execution times up to 80%, making it a worthwhile investment for faster leaderboards, especially on busy forums. I would recommend only implementing the "High Priority" indexes.
The indexes with the biggest benefits (if using that metric) are:
- idx_post_thread_state_date_user
- idx_post_user_date_state_thread
- idx_reaction_content_user_date_id
- idx_reaction_user_date
SQL:
-- Index for ANY and ALL Scoring Metrics
-- Why: Supports user filtering for all metrics via getWhereSql (user_state, user_group_id, wutc_exclude_users).
-- Impact: Saves 10–20% (0.01–0.05s for 1,000 users, 0.05–0.1s for 10,000).
CREATE INDEX idx_user_state_group_id ON xf_user (user_state, user_group_id, user_id);
CREATE INDEX idx_post_date_message_id ON xf_post (post_date, message_state, post_id); -- shared with both thread metrics
-- milestones_achieved (Low Priority)
-- Why: Least intensive, small tables (xf_user_trophy: 10K–100K rows, xf_trophy: ~100 rows).
-- Impact: Saves 50–70% (0.05–0.2s for 1,000 users, 0.1–0.4s for 10,000).
-- Note: Skip xf_trophy and xf_user_trophy for small forums (<1,000 users) if performance is acceptable.
CREATE INDEX idx_user_trophy_user_date_id ON xf_user_trophy (user_id, award_date, trophy_id);
CREATE INDEX idx_trophy_id_points ON xf_trophy (trophy_id, trophy_points);
-- threads_created (Moderate Priority)
-- Why: Moderately intensive, uses xf_thread (100K–1M rows) and xf_post (1–10M rows).
-- Impact: Saves 50–70% (0.1–0.2s for 1,000 users, 0.2–0.4s for 10,000).
CREATE INDEX idx_thread_user_state_node_id ON xf_thread (user_id, discussion_state, node_id, first_post_id);
-- posts_made (Moderate Priority)
-- Why: Moderately intensive, uses large xf_post (1–10M rows).
-- Impact: Saves 50–80% (0.2–0.4s for 1,000 users, 0.4–0.7s for 10,000).
CREATE INDEX idx_post_user_date_state_thread ON xf_post (user_id, post_date, message_state, thread_id);
CREATE INDEX idx_thread_id_state_node ON xf_thread (thread_id, discussion_state, node_id);
-- reactions_given (High Priority)
-- Why: Intensive, large xf_reaction_content (1–10M rows).
-- Impact: Saves 50–80% (0.2–0.8s for 1,000 users, 0.6–1.5s for 10,000).
CREATE INDEX idx_reaction_user_date ON xf_reaction_content (reaction_user_id, reaction_date);
-- reactions_received (High Priority)
-- Why: Intensive, large xf_reaction_content (1–10M rows) with join.
-- Impact: Saves 50–80% (0.3–0.8s for 1,000 users, 0.8–1.8s for 10,000).
CREATE INDEX idx_reaction_content_user_date_id ON xf_reaction_content (content_user_id, reaction_date, reaction_id);
CREATE INDEX idx_reaction_id_score ON xf_reaction (reaction_id, reaction_score);
-- thread_replies_received (High Priority)
-- Why: Most intensive, joins large xf_post (1–10M rows) and xf_thread (100K–1M rows).
-- Impact: Saves 50–80% (0.4–0.9s for 1,000 users, 1–2.5s for 10,000).
-- Note: Avoid in small forums unless necessary.
CREATE INDEX idx_thread_state_user_id ON xf_thread (discussion_state, user_id, thread_id, first_post_id);
CREATE INDEX idx_post_thread_state_date_user ON xf_post (thread_id, message_state, post_date, user_id);