PDA

View Full Version : Help with a query?


jawatkin
04 Jun 2008, 13:39
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,5 8139,26655,87722,92189,91829,91839,91735,83221,90841,91242,78676,59616,74299,890 98,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,8 9532,89329,77307,84687,85868,89418,89250,89125,74632,88846,52762,67375,84406,885 63,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,15 805,86617,86583,47361,86421,85800,85375,69145,86081,72421,81630,85609,85973,8536 6,66318,84467,85916,80439,85321,83430,85215,85513,85367,85360,5188,85193,83294,8 1855,84942,84752,83762,84834,63315,55247,84594,82738,77622,84090,83940,52905,598 52,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,5 9623,55527,74694,32751,73849,77465,77205,77107,77116,76037,71158,76322,76311,762 72,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,71 994,72224,70027,71481,71441,71334,45346,54666,71108,70757,40499,70889,64716,6928 5,33064,33718,70020,5509,69422,23976,8802,10093,60952,46917,48585,70004,69772,68 151,39941,69207,69657,69669,25307,67883,60081,55051,28504,69021,62915,41764,6873 2,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,66 548,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,22 832,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,594 66,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,4 4646,56684,47435,28023,56443,56216,56434,56118,56119,31451,55754,55797,34556,312 35,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,5 3938,32542,53057,45213,19828,39465,13277,37014,17779,19215,43813,41529,47608,519 22,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,483 13,36343,27628,48108,48191,13292,48266,2823,34172,38694,47919,9246,41332,44456,3 9315,20818,43711,14092,22352,46641,37694,21596,35048,42810,45305,37727,10619,440 16,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,42 665,42665,16677,42626,33031,42429,42388,21834,42164,40734,41770,41337,30320,2831 9,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,27 142,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,1 7895,32984,33343,14782,13239,32893,28747,12542,32193,22955,24486,26827,12114,224 24,12772,3104,4832,31019,30760,27715,29876,28924,30206,19787,7570,9959,29055,284 54,28624,11112,18676,16328,25702,28008,7414,10878,26607,7414,18657,19997,20199,1 1641,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,235 84,23160,8691,5352,8794,22535,15892,23113,19087,13512,22830,22506,21037,12653,19 786,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,4239 7,16959,19607,18958,14664,18940,7571,18329,15953,18525,3986,3816,9755,10984,1560 7,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
04 Jun 2008, 15:06
Doing a search of "post.userid, COUNT(*) AS replycount" in the files shows me this query:
$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
04 Jun 2008, 17:12
Yikes... Well, with about 90,000 users, I guess the coventried users start to add up. I'll move the coventried users to banned. :)