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

Fixed Performance slow when viewing member list on large forums

Discussion in 'Resolved Bug Reports' started by Pepelac, Apr 30, 2013.

  1. Pepelac

    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.
     

    Attached Files:

    Andy.N, AlexT, CyberAP and 1 other person like this.
  2. AlexT

    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
     
  3. Mike

    Mike XenForo Developer Staff Member

    These issues are actually sorted for 1.2 already. :)
     
    Walter, Yoskaldyr, Pepelac and 3 others like this.
  4. Yoskaldyr

    Yoskaldyr Well-Known Member

    How about mysql skip/limit performance issues on large tables?
    Code:
    SELECT user.*
    ..........
    ORDER BY user.username
    LIMIT 20 OFFSET 400000
    
    Pagination will be removed?
     

Share This Page