Performance problem on /privatemessage/index

[code]

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:

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

[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]*************************** 1. row ***************************[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]          id: 1[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px] select_type: SIMPLE[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]       table: starter[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]        type: range[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]possible_keys: PRIMARY,node_parent,nodeid[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]         key: node_parent[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]     key_len: 4[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]         ref: NULL[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]        rows: 1200[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]       Extra: Using where; Using index; Using temporary; Using filesort[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]*************************** 2. row ***************************[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]          id: 1[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px] select_type: SIMPLE[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]       table: node[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]        type: ref[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]possible_keys: node_starter,node_approved,node_ctypid_userid_dispo_idx,nodeid,contenttypeid_parentid[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]         key: node_starter[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]     key_len: 4[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]         ref: myapple_vbulletin_production.starter.nodeid[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]        rows: 1[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]       Extra: Using index condition; Using where[/SIZE][/FONT][/COLOR]
[COLOR=#60605E][FONT=Source Code Pro for Powerline][SIZE=16px]2 rows in set (0,00 sec)[/SIZE][/FONT][/COLOR]

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

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?

[Zachery]

Are you on 5.1.3?

[code]

yes