Fixed Performance slow when viewing member list on large forums

Pepelac

Well-known member
There are 3 queries which are too slow: two for selecting users for sidebar blocks and one for geting total user count (see attachment).

I'd recommend to cache those results with rebuilding caches on user registration, banning or user_state changing.
 

Attachments

  • Screen Shot 2013-04-30 at 11.17.54 .png
    Screen Shot 2013-04-30 at 11.17.54 .png
    87.3 KB · Views: 49

AlexT

Well-known member
Or, as a temporary workaround which is what I've done by overwriting the getUsers() and countUsers() methods for specific cases, disable the conditionals to include only valid (=confirmed) and non-banned members. Basically traditional "vBulletin behavior". ;)

Code:
SELECT COUNT(*)
from xf_user AS user
WHERE (user.user_state = 'valid') AND (user.is_banned = 0)
 
1 row (0.574 s)
 
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE user ref user_state user_state 1 const 100302 Using where

Code:
SELECT COUNT(*)
from xf_user AS user
 
1 row (0.010 s)
 
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE user index NULL user_state 1 NULL 200604 Using index
 
Top