👥 Membership Sites = Database Scalability
Membership sites grow rapidly. Database optimization ensures smooth scaling with thousands of members and interactions.
📝 Membership Database Optimization
# Member Activity Cleanup
-- Remove abandoned memberships
DELETE FROM wp_usermeta
WHERE meta_key = 'membership_status'
AND meta_value = 'pending'
AND user_id IN (
SELECT user_id
FROM wp_usermeta
WHERE meta_key = 'membership_date'
AND meta_value < DATE_SUB(NOW(), INTERVAL 30 DAY)
);
# Clean Member Reports
-- Archive old activity logs
CREATE TABLE wp_members_activity_archive LIKE wp_members_activity;
INSERT INTO wp_members_activity_archive
SELECT * FROM wp_members_activity
WHERE activity_date < DATE_SUB(NOW(), INTERVAL 90 DAY);
DELETE FROM wp_members_activity
WHERE activity_date < DATE_SUB(NOW(), INTERVAL 90 DAY);
# Optimize Member Meta
-- Remove orphaned meta
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;
-- Remove duplicate meta keys
DELETE pm1 FROM wp_usermeta pm1
INNER JOIN wp_usermeta pm2
WHERE pm1.umeta_id > pm2.umeta_id
AND pm1.user_id = pm2.user_id
AND pm1.meta_key = pm2.meta_key;
# Cache Member Queries
-- Use Redis for member data cache
-- Cache member roles and permissions
-- Cache member subscriptions
-- Cache member activity feed
# Index Strategy for Member Tables
CREATE INDEX idx_member_status
ON wp_usermeta (meta_key, meta_value);
CREATE INDEX idx_member_date
ON wp_usermeta (user_id, meta_key, meta_value);
CREATE INDEX idx_member_email
ON wp_users (user_email);
🎯 Performance Monitoring for Membership
# Monitor Active Members
SELECT
COUNT(DISTINCT user_id) as active_members,
DATE(login_date) as date
FROM wp_member_logins
WHERE login_date > DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(login_date)
ORDER BY date DESC;
# Check Query Performance
SELECT
query_time,
lock_time,
rows_sent,
rows_examined,
sql_text
FROM mysql.slow_log
WHERE sql_text LIKE '%wp_usermeta%'
OR sql_text LIKE '%wp_posts%'
AND sql_text NOT LIKE '%%wp_options%%'
ORDER BY query_time DESC
LIMIT 10;
# Member Growth Analysis
SELECT
DATE(registration_date) as date,
COUNT(*) as new_members,
SUM(COUNT(*)) OVER (ORDER BY DATE(registration_date))
as cumulative_members
FROM wp_users
WHERE registration_date > DATE_SUB(NOW(), INTERVAL 90 DAY)
GROUP BY DATE(registration_date)
ORDER BY date;
# Churn Rate Calculation
SELECT
DATE(activity_date) as date,
COUNT(DISTINCT user_id) as active_users,
LAG(COUNT(DISTINCT user_id)) OVER (ORDER BY DATE(activity_date))
as previous_active,
((LAG(COUNT(DISTINCT user_id)) OVER (ORDER BY DATE(activity_date))
- COUNT(DISTINCT user_id)) /
LAG(COUNT(DISTINCT user_id)) OVER (ORDER BY DATE(activity_date)))
* 100 as churn_rate
FROM wp_member_activity
WHERE activity_date > DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(activity_date)
ORDER BY date;
✅ Membership Site Best Practices
- ✓ Implement member data caching
- ✓ Use database partitioning for logs
- ✓ Regular archiving of old data
- ✓ Monitor query performance weekly
- ✓ Scale database with membership growth
Membership sites need proactive database management. A slow query can mean lost members. Regular optimization keeps your community growing.
