AndrewSimm
Well-known member
That is a great callout. Here is what I can tell you.I'd like to check with you about how efficient the queries are. Quite a few addons we use have had slow queries and we had to contact the authors to request optimisation. We have 430,000 members.
Only three of the queries used don't use the finder system, so the efficiency of those queries should be inline with other XF queries. The three queries don't use finder still use "fetchAll" and prepared statements. When I run the queries with Sequel Pro on my test board (about a dozen users) it takes 9.9ms. When I run the query using Sequel Pro on my main board (16.4k users, 4,7m posts) it takes 99ms. When I run the query a second time, it takes about 25% of the time it took to run the original query.
If you are able to, run this query in whatever SQL tool you use and let me know how it performs.
SQL:
select xf_user.user_id, xf_user.username, xf_user.message_count, count(xf_user_ignored.ignored_user_id) as ignored_count
from xf_user
inner join xf_user_ignored on xf_user.user_id = xf_user_ignored.ignored_user_id
group by xf_user.user_id, xf_user.username, xf_user.message_count, xf_user.warning_points
having count(xf_user_ignored.ignored_user_id) > 0
order by ignored_count desc
limit 40