Announcement

Collapse
No announcement yet.

Performance problem on /privatemessage/index

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • code
    replied
    yes

    Leave a comment:


  • Zachery
    replied
    Are you on 5.1.3?

    Leave a comment:


  • code
    started a topic Performance problem on /privatemessage/index

    Performance problem on /privatemessage/index

    I migrated big vbulletin 4 forum to vbulletin 5 and have some issuses with performance.

    For example when administrator visit /privatemessage/index there is called method fetchPendingPosts and it produce query:

    Code:
    SELECT DISTINCT node.nodeid, node.contenttypeid FROM node AS starter INNER JOIN node AS node ON (node.starter = starter.nodeid) WHERE ( (starter.parentid IN (1,2,3,4,6,13,14,15,16,17,18,19,20,21,22,3379659,3379660,3379661,3379662,3379663,3379664,3379665,3379666,3379667,3379668,3379669,3379670,3379671,3379672,3379673,3379674,3379675,3379676,3379677,3379678,3379679,3379680,3379681,3379682,3379683,3379684,3379685,3379686,3379687,3379688,3379689,3379690,3379691,3379692,3379693,3379694,3379695,3379696,3379697,3379698,3379699,3379700,3379701,3379702,5,3,4036558,7,8,9,10,11,12,23,24,26,36,38,43,47,48,53,77,27,31,42,44,56,57,72,78,25,29,32,33,46,49,55,66,28,51,30,34,40,41,45,74,59,37,52,64,68,69,70,71,73,75,76,79,39,58,35,60,63,81,89,119,88,90,104,108,109,112,86,87,82,83,91,92,97,101,102,103,94,95,85,128,93,96,125,114,115,80,84,99,100,120,98,118,106,121,107,122,130,105,113,116,126,127,129,146,134,137,138,139,140,144,150,133,136,141,142,143,145,151,131,132,135,147,149,152,153,154,155,4036559,4036560,4036561,4036562,4036563,4036564,4036565,4036566,4036567,4036568,4036569,4036570,4036571,4036572,4036573,4036574,4036575,4036576,4036577,4036578,4036579,4036580,4036581,4036582,4036583,4036584,4036585,4036586,4036587,4036588,4036589,4036590,4036591,4036592,4036593,4036594,4036595,4036596,4036597,4036598,4036599,4036600,4036601,4036602,4036603,4036604,4036605,4036606,4036607,4036608,4036609,4036610) AND node.approved = 0 AND node.showpublished <> 0) OR (starter.parentid IN (13) AND node.showpublished < 1) )AND node.contenttypeid NOT IN (22,29) ORDER BY node.publishdate DESC LIMIT 20
    This query takes about 40seconds to run.

    EXPLAIN
    Code:
    *************************** 1. row ***************************
              id: 1
     select_type: SIMPLE
           table: starter
            type: range
    possible_keys: PRIMARY,node_parent,nodeid
             key: node_parent
         key_len: 4
             ref: NULL
            rows: 1200
           Extra: Using where; Using index; Using temporary; Using filesort
    *************************** 2. row ***************************
              id: 1
     select_type: SIMPLE
           table: node
            type: ref
    possible_keys: node_starter,node_approved,node_ctypid_userid_dispo_idx,nodeid,contenttypeid_parentid
             key: node_starter
         key_len: 4
             ref: myapple_vbulletin_production.starter.nodeid
            rows: 1
           Extra: Using index condition; Using where
    2 rows in set (0,00 sec)

    node table has about 2.3 milions records. starter.parentid IN in this query for this user returns about 0.5m records

    Code:
    SELECT count(*) FROM node AS starter WHERE starter.parentid IN (1,2,3,4,6,13,14,15,16,17,18,19,20,21,22,3379659,3379660,3379661,3379662,3379663,3379664,3379665,3379666,3379667,3379668,3379669,3379670,3379671,3379672,3379673,3379674,3379675,3379676,3379677,3379678,3379679,3379680,3379681,3379682,3379683,3379684,3379685,3379686,3379687,3379688,3379689,3379690,3379691,3379692,3379693,3379694,3379695,3379696,3379697,3379698,3379699,3379700,3379701,3379702,5,3,4036558,7,8,9,10,11,12,23,24,26,36,38,43,47,48,53,77,27,31,42,44,56,57,72,78,25,29,32,33,46,49,55,66,28,51,30,34,40,41,45,74,59,37,52,64,68,69,70,71,73,75,76,79,39,58,35,60,63,81,89,119,88,90,104,108,109,112,86,87,82,83,91,92,97,101,102,103,94,95,85,128,93,96,125,114,115,80,84,99,100,120,98,118,106,121,107,122,130,105,113,116,126,127,129,146,134,137,138,139,140,144,150,133,136,141,142,143,145,151,131,132,135,147,149,152,153,154,155,4036559,4036560,4036561,4036562,4036563,4036564,4036565,4036566,4036567,4036568,4036569,4036570,4036571,4036572,4036573,4036574,4036575,4036576,4036577,4036578,4036579,4036580,4036581,4036582,4036583,4036584,4036585,4036586,4036587,4036588,4036589,4036590,4036591,4036592,4036593,4036594,4036595,4036596,4036597,4036598,4036599,4036600,4036601,4036602,4036603,4036604,4036605,4036606,4036607,4036608,4036609,4036610)
    ​How can we optimalize this?

Related Topics

Collapse

Working...
X