Fixed Performance slow when viewing member list on large forums

[Pepelac]

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.

 

[AlexT]

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

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

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

 

[Mike]

These issues are actually sorted for 1.2 already.

 

[Yoskaldyr]

These issues are actually sorted for 1.2 already.

How about mysql skip/limit performance issues on large tables?

SELECT user.*
..........
ORDER BY user.username
LIMIT 20 OFFSET 400000

Pagination will be removed?