Help with a query?

[jawatkin]

I'm trying to track down the source of this query, as it is a slow executing query. Can anyone tell me if they recognize the query, or perhaps how it is being activated? From what I can tell, it is a user-generated, just not sure what they are doing to make the query and I want to disable its use!

# Time: 080604  7:25:43
# User@Host: 
# Query_time: 20  Lock_time: 0  Rows_sent: 38  Rows_examined: 174223
SELECT post.userid, COUNT(*) AS replycount
                        FROM post AS post
                        INNER JOIN thread AS thread ON (post.threadid = thread.threadid)
WHERE post.userid IN (93354,93428,92835,93093,91335,91931,92944,81962,41944,92731,74343,62910,55591,58139,26655,87722,92189,91829,91839,91735,83221,90841,91242,78676,59616,74299,89098,90957,36267,90919,90719,41188,26813,12700,20724,90600,61023,90662,89724,62193,83340,67298,87000,72376,90024,76411,90011,88893,89924,89918,87053,89828,89785,89532,89329,77307,84687,85868,89418,89250,89125,74632,88846,52762,67375,84406,88563,79804,88241,88537,88221,88331,88202,27920,87879,80045,76699,82702,76910,87570,87501,87385,87357,87250,87167,87366,71687,86867,86790,86759,86810,73825,2418,15805,86617,86583,47361,86421,85800,85375,69145,86081,72421,81630,85609,85973,85366,66318,84467,85916,80439,85321,83430,85215,85513,85367,85360,5188,85193,83294,81855,84942,84752,83762,84834,63315,55247,84594,82738,77622,84090,83940,52905,59852,83173,73583,64262,70547,78890,73537,82767,81704,78386,77803,82146,78733,68519,76963,78725,22575,75246,74665,67310,66875,65305,64172,79085,79661,73169,78492,59623,55527,74694,32751,73849,77465,77205,77107,77116,76037,71158,76322,76311,76272,73941,73129,10334,75927,69807,74644,52766,59245,67549,75207,71071,69203,65623,2458,73403,10259,73364,73445,36229,73095,72876,52766,72513,72506,60420,72310,71994,72224,70027,71481,71441,71334,45346,54666,71108,70757,40499,70889,64716,69285,33064,33718,70020,5509,69422,23976,8802,10093,60952,46917,48585,70004,69772,68151,39941,69207,69657,69669,25307,67883,60081,55051,28504,69021,62915,41764,68732,29998,68687,38246,67946,67981,56529,54277,68486,60215,44465,67933,16357,60939,66651,54375,67561,28462,65441,67325,25669,59469,58698,66297,64327,66781,63063,66548,63095,66325,52611,2552,47898,40057,61435,65737,14188,65586,65403,10694,61752,51622,57965,54630,64696,64576,3966,26579,64352,56520,56520,60490,32762,64009,22832,31832,18153,57106,61913,8678,62409,52237,8678,61652,24459,53002,21144,59983,42717,32842,49582,47352,59819,60538,4103,21239,59661,59108,26860,59602,59567,59466,51727,21802,53493,53447,37122,58474,47398,53371,52776,25367,58282,58276,37156,57820,57872,19659,57762,54832,57812,57643,56495,48352,55380,42433,55072,56897,44646,56684,47435,28023,56443,56216,56434,56118,56119,31451,55754,55797,34556,31235,53183,53183,44079,33310,35435,23592,54713,42012,52188,54924,48737,21850,30822,49665,50503,13635,54321,54016,50658,53983,48296,53645,36223,53939,53939,53936,53938,32542,53057,45213,19828,39465,13277,37014,17779,19215,43813,41529,47608,51922,52438,52776,51744,52465,48061,50527,34190,51409,51427,49526,50042,3981,48771,38624,43693,38580,50307,38978,37404,48310,7442,47814,47816,38201,42246,48451,48313,36343,27628,48108,48191,13292,48266,2823,34172,38694,47919,9246,41332,44456,39315,20818,43711,14092,22352,46641,37694,21596,35048,42810,45305,37727,10619,44016,45074,40282,45575,41845,45709,45487,45291,17516,45441,43492,38642,40553,44211,44358,41822,13442,22184,43843,8394,33016,32228,43326,43330,27421,42802,42161,42665,42665,16677,42626,33031,42429,42388,21834,42164,40734,41770,41337,30320,28319,41430,38269,39794,38266,35742,37941,41106,36954,40491,22862,40777,38550,37210,30421,25367,37731,39666,39660,36641,39399,21961,38785,38959,21940,38752,38684,27142,6392,24102,37521,20775,35055,38198,28340,28471,28624,31019,36630,17620,22600,35927,33009,13367,35958,35872,35743,35741,35723,35686,34953,31935,24391,22255,17895,32984,33343,14782,13239,32893,28747,12542,32193,22955,24486,26827,12114,22424,12772,3104,4832,31019,30760,27715,29876,28924,30206,19787,7570,9959,29055,28454,28624,11112,18676,16328,25702,28008,7414,10878,26607,7414,18657,19997,20199,11641,16891,5625,26709,19107,21192,21185,25136,25687,16317,25841,13176,6309,25946,17901,23599,25709,18308,13876,8615,12756,19667,20294,23258,24460,10021,4246,23584,23160,8691,5352,8794,22535,15892,23113,19087,13512,22830,22506,21037,12653,19786,17089,10916,21999,15341,11110,21870,21870,12475,8494,20748,5186,21221,15059,21350,21147,21142,20683,19418,20681,19882,14917,9723,9559,8361,16720,7540,17576,12421,20499,17850,20030,8251,19021,10575,19399,19286,8846,19541,15838,17444,42397,16959,19607,18958,14664,18940,7571,18329,15953,18525,3986,3816,9755,10984,15607,17631,16841,15567,17173,17073,16839,14511,9060,5458,13514,7697,6412,2599,15544,3531,3862,4345,4372,5056,5169,9630,5272,5428,5478,5868,5988,6008,6084,6150,6560,6926,7107,7529,7693,8074,8186,8209,8482,8512,8646,8812,8824,9065,9082,9615,9685,9831,9943,11278,11398,11510,11792,12041,14069)
                                AND post.visible = 1
                                AND thread.forumid = 185
                        GROUP BY post.userid;

[Lynne]

Doing a search of "post.userid, COUNT(*) AS replycount" in the files shows me this query:

PHP Code:

        $tachy_db = $vbulletin->db->query_read("
            SELECT post.userid, COUNT(*) AS replycount
            FROM " . TABLE_PREFIX . "post AS post
            INNER JOIN " . TABLE_PREFIX . "thread AS thread ON (post.threadid = thread.threadid)
            WHERE post.userid IN ($coventry)
                AND post.visible = 1
                AND thread.forumid = $forumid
            GROUP BY post.userid
        "); 


On line 166 in includes/functions_databuild.php The same query shows up on line 346 of the same file.

Perhaps you might want to consider taking some users out of coventry and using another banning method.

[jawatkin]

Yikes... Well, with about 90,000 users, I guess the coventried users start to add up. I'll move the coventried users to banned.