No announcement yet.

Slow query - Where is this coming from?

  • Filter
  • Time
  • Show
Clear All
new posts

  • Slow query - Where is this coming from?

    This is in my slow query log... over and over and over, except the LIMIT increments by 30 each time. It takes 12 seconds to run each time it runs (and it constantly runs). Maybe not coincidentally, I am having serious load issues right now:

    SELECT user.*,usertextfield.*,userfield.*, user.userid, options,
    IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
    ,IF((NOT(options & 1024) AND (user.usergroupid IN (-1))), 0, reputation) AS reputationscore,level

    , IF((options & 512 AND user.userid <> 0), 1, lastactivity) AS lastvisittime

    FROM user AS user
    LEFT JOIN usertextfield AS usertextfield ON(usertextfield.userid=user.userid)
    LEFT JOIN userfield AS userfield ON(userfield.userid=user.userid)
    LEFT JOIN reputationlevel AS reputationlevel ON(user.reputationlevelid=reputationlevel.reputationlevelid)

    WHERE 1=1
    AND user.usergroupid IN (-1,6,9,7,2,5)
    ORDER BY user.username asc
    LIMIT 153120, 30;

    Where is that coming from? Some scheduled task or something? Could this be from people looking at the member listing pages?

    Last edited by RyanD789; Fri 15 Sep '06, 9:45pm.

  • #2
    Nevermind. It is from the member listing. Takes 10 - 12 seconds on average! Killing me. Am I the first one? Any fixes besides disabling the member listing?



    • #3
      It's the problem that vBulletin isn't optimized for big numbers.

      Same happens with the forum search (queries with hundreds of thousands threadids in them).

      In your case it's most likely a spider that follows the 1, 2, 3, ... at the bottom of the page.

      You could just disable the normal browsing of the member list (I mean who needs to browse a 350k member list) and require a search input?


      • #4
        Also quite interesting that on the member list is cut off at page 501.


        • #5
          FYI... I disabled the member listing for now and my forum is running 50 times better and faster. Seriously. Those member listing queries were killing the server!

          Anybody with a large forum that is having performance issues should look into this!



          widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.