1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Help to understand and maybe optimize a slow and CPU intensive query

Discussion in 'XenForo Development Discussions' started by imthebest, Apr 21, 2015.

  1. imthebest

    imthebest Formerly Super120

    Hi guys,

    I'm using an add-on that adds a "Most Followers" tab at the Notable Members list (I can't mention the author because he got recently banned here). Well the problem is that when viewing the "Most Followers" tab at the Notable Members list I have a slow query that uses around 85% of my CPU:


    Isn't this due to a missing index? As far as I know when a query says "using filesort" it means that an index would be of help. For example take a look at the stock XF query that processes the "Most Messages" tab at the Notable Members list:


    As you can see the stock XF query doesn't "uses filesort".

    How I can improve the performance of the query being used on the "Most Followers" add-on? Maybe because XF itself doesn't include a "Most Followers" tab then it doesn't have the proper index to help that process?

    Last edited: Apr 22, 2015
  2. Brogan

    Brogan XenForo Moderator Staff Member

    If you wish to continue using that add-on, you can contact the developer on their own site for support.
  3. imthebest

    imthebest Formerly Super120

    I know Brogan and thanks for the tip but it is not that I want to ask directly him about this, I just want to ask other developers here for some input to help me understand why the query is slow and CPU intensive and what could be done in order to improve its performance.

    @Daniel Hood I see that you have an add-on that aside from other things also add a "Most Followers" tab to the Notable Members list. I can see that on your add-on you say this:

    So without caching and without the recent activity constraints (i.e. just like the default behavior of the stock XF tabs at the Notable Members list) does your query performs as bad as this one? Have you added any indexes as part of your add-on installation process to help the query that gets the list of the most followed members?

    Thanks in advance for your input Dan.
    Last edited: Apr 22, 2015
  4. Luke F

    Luke F Well-Known Member

    The entire query would need to be inverted to a group by on xf_user_follow. Not perfectly efficient, but dramatically more so than an unoptimisable subquery per user. The query optimiser isn't smart enough to figure out what is being done by the subquery, so it's counting followers for each user rather than a fast aggregated count of a group by.

    In other words, you can't fix this by just adding an index. At minimum the query needs rewritten, at best follower_count needs synced to a per-user field
    Last edited: Apr 22, 2015
    imthebest likes this.
  5. Daniel Hood

    Daniel Hood Well-Known Member

    The query is slow because it's not using an indexed column correctly. I'll look into fixing it, thank you.
  6. imthebest

    imthebest Formerly Super120

    @Daniel Hood I'm not using your add-on. This slow query isn't from your add-on. I was asking you if your add-on does have the same problem.
  7. Daniel Hood

    Daniel Hood Well-Known Member

    Sorry. I just skimmed the thread due to limited time. I've had no complaints about that tab performing poorly. I can't say with certainty that it wouldn't have the same issue on your board, in the same conditions, though.
  8. imthebest

    imthebest Formerly Super120

    It's all good, don't worry. Thanks for your input!

    May I ask you how would you improve the performance of that query? Luke Foreman at post #4 gave me some insights but then you said that the query is slow because it wasn't using an indexed column properly. What exactly would you change in the query to make it perform better?
  9. Luke F

    Luke F Well-Known Member

    You need to rewrite the query so it looks like:

    select [user stuff], count(*) as follower_count
    from xf_user_follow
    inner join xf_user user on follow_user_id = user.user_id [inner rather than left is significant]
    [other joins, keep as left joins]
    [where clause]
    group by follow_user_id
    order by follower_count desc
    limit 20
    There's still a sort and a temporary table involved (completely unavoidable without introducing an additional user field and updating the necessary datawriters to keep it in sync), but it shouldn't be quite as cpu intensive
    imthebest likes this.

Share This Page