#5063 - Use get_table_count_approx for forum drivers / Do not count bots created by phpBB towards number of members

By PDStig
Added 19th Nov 2022, 1:11 PM
2 views

Identifier	#5063
Issue type	Feature request or suggestion
Title	Use get_table_count_approx for forum drivers / Do not count bots created by phpBB towards number of members
Status	Open
Handling member	Deleted
Addon	core_forum_drivers
Description	CNS uses get_table_count_approx for counting members/topics/posts, because InnoDB is very slow. Ideally forum drivers should use this to. Also: phpBB creates a bunch of bot accounts for web crawlers. Do not count these towards total site members in Composr when using the phpBB driver.
Steps to reproduce
Funded?	No

The system will post a comment when this issue is modified (e.g., status changes). To be notified of this, click "Enable comment notifications".

Rating

Unrated

Comments

By Guest posted 20th Nov 2022, 10:17 PM

From our POV it is inefficient to try and work out what are bots, as those no direct way to do it - we'd have to check group membership, meaning a join and less efficient SQL counting mechanism.

I wonder how phpBB itself handles this. Does it not count bots? Does it show a total at all?

Regardless, this isn't a big issue, we just wanted to raise it on the tracker in case people care and to have the discussion.

By Guest posted 20th Nov 2022, 10:26 PM

It is possible to get the member count with 2 queries: first, grab the bot group ID, second, run a COUNT on the users table for all users whose primary group does not match that bot group ID (primary group is on the users table, so no JOIN is needed)

By Guest posted 21st Nov 2022, 3:20 AM

Is this going to be reliable or does it hard-code a group name that might be edited / vary by language?

Also in the ideal world we'd be using get_table_count_approx for the member/topic/post count queries in the forum drivers, as counting full table rows on InnoDB is very slow. We're currently only doing that for CNS. If we do that, we either need to not do the filtering or actually have a third query for the number of bots to subtract (rather than doing it as part of a WHERE clause). I'll add that to this issue.

By Guest posted 21st Nov 2022, 9:09 AM

It looks like it would be reliable; the group name is BOTS and cannot be edited in the phpBB interface. I ran a Spanish install and it was still BOTS in the database (the other group names were also the same).