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

[imthebest]

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?

Thank,
Super120

 

[Paul B]

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

 

[imthebest]

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:

As part of 1.1.0 I added a "Most Followers" tab to the notable members page. This works with the caching system along with the constraints of recent activity based on last post or last activity. Even if it's not cached, it shouldn't add too much to the query load.

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.

 

[Luke F]

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

 

[Daniel Hood]

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

 

[imthebest]

@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.

 

[Daniel Hood]

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.

 

[imthebest]

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?

 

[Luke F]

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