Announcement

Collapse
No announcement yet.

Tags Slow Query

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

  • [Forum] Tags Slow Query

    I am facing a big problem in relation to this query, the database is 2.8GB in size with current

    # Query_time: 5.657942 Lock_time: 0.000211 Rows_sent: 70 Rows_examined: 5014272
    SET timestamp=1299696085;
    SELECT data.tagid, data.tagtext, SUM(data.searchcount) AS searchcount
    FROM
    (
    SELECT tagcontent.tagid, tag.tagtext, COUNT(*) AS searchcount
    FROM vb_tagcontent AS tagcontent
    INNER JOIN vb_tag AS tag ON (tagcontent.tagid = tag.tagid) INNER JOIN vb_thread AS thread ON (tagcontent.contentid = thread.threadid)
    WHERE tagcontent.contenttypeid IN (2) AND
    tagcontent.dateline > 1268160079 AND thread.open <> 10 AND thread.visible = 1 AND thread.forumid IN (484,6,43,44,129,197,59,437,3,16,73,99,101,102,100,76,86,115,80,81,252,253,84,112,117,103, 185,205,254,355,360,361,391,449,450,451,452,453,454,530,532,74,75,77,78,79,82,534,535,83,1 13,31,69,520,18,33,55,63,250,521,537,538,539,35,57,34,56,128,36,53,54,540,126,127,125,134, 251,20,42,135,207,208,503,504,485,561,483,559,558,557,560,95,283,527,17,327,328,329,330,33 1,332,436,333,431,249,130,133,131,132,124,32,522,120,138,529,238,239,137,526,50,109,363,11 4,107,106,380,108,362,385,110,111,518,524,455,204,244,245,246,247,308,309,310,311,517,66,4 39,440,441,442,194,209,382,196,195,96,443,444,97,98,144,143,523,19,37,38,39,40,72,41,525,1 18,182,183,184,186,187,188,67,21,119,121,122,123,105,14,364,365,505,366,367,368,369,370,37 1,372,373,377,378,379,374,375,376,9,198,199,200,201,531,212,213,214,215,216,217,218,219,22 0,221,222,223,224,225,227,381,226,71,536,313,314,315,316,317,318,320,177,178,179,180,181,3 12,319,139,228,231,229,230,388,386,140,232,141,142,8,206,210,211,4,104,399,23,515,22,514,2 4,25,64,516,68,203,445,446,447,448,12,284,286,287,288,289,290,291,292,293,285,294,295,296, 297,298,299,300,301,302,303,304,305,306,307,13,334,335,340,341,336,348,349,350,337,338,342 ,343,344,339,345,346,347,351,10,26,88,89,90,91,92,116,5,533,87,240,241,242,243,93,27,519,2 9,49,30,392,393,394,395,396,397,398,51,60,61,62,528,28,490,491,492,493,494,495,501,498,497 ,499,500,48,47,324,325,326,52)
    GROUP BY tagcontent.tagid, tag.tagtext
    UNION ALL
    SELECT tagcontent.tagid, tag.tagtext, COUNT(*) AS searchcount
    FROM vb_tagcontent AS tagcontent
    INNER JOIN vb_tag AS tag ON (tagcontent.tagid = tag.tagid) JOIN vb_cms_node AS cms_node ON
    tagcontent.contenttypeid = cms_node.contenttypeid AND tagcontent.contentid = cms_node.contentid

    WHERE tagcontent.contenttypeid IN (18,20) AND
    tagcontent.dateline > 1268160079 AND cms_node.publishdate < 1299696079
    GROUP BY tagcontent.tagid, tag.tagtext
    ) AS data
    GROUP BY data.tagid, data.tagtext
    ORDER BY searchcount DESC
    LIMIT 70;
widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
Working...
X