You've been around since 2018, so you've been here while I've had to rebuild the forum from SQL dumps. You've undoubtedly seen me mention that the site rebuilds often take hours because the table that holds the reactions is over 100 million rows long, right?
I never even considered this until this week, but do you know what
else is over 100 million rows long and takes hours to restore?
Code:
> select count(1) from xf_user_alert;
+-----------+
| count(1) |
+-----------+
| 103540085 |
+-----------+
1 row in set (38.346 sec)
> select count(1) from xf_reaction_content;
+-----------+
| count(1) |
+-----------+
| 132064188 |
+-----------+
1 row in set (6 min 44.330 sec)
That's right! The table that stores user alerts! It's currently 103,540,085 rows long (and growing), and that's
after I've truncated it in the past. That's because every single time XenForo records a rating, there is almost always a corresponding user that gets an alert, so the number of SQL queries that go into hundred million row tables is actually twice what it would be without them.
So basically, a part of the server has been continuously dedicated to updating, selecting, and indexing a second table continuously because it is being added to every single time someone clicks a reaction so that users can be notified that this person clicked a button on your post despite the fact that this has absolutely no consequences.