Announcement

Collapse
No announcement yet.

updateCountsForDepth Query

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

  • Font Size
    #1

    updateCountsForDepth Query

    Hello,



    I was trying to track down an issue that was causing delete / undelete of topics to be extremely slow, taking almost 2 minutes sometimes. The slow query log showed the following query, that I was able to track down in querydefs.





    PHP Code:



    /*
    * Note that this will *not* update any nodes that don't have children. The counts for nodes without
    * children should always be 0. This is intended for updating a subtree based on a change to the
    * parent so leaf nodes should not be affected.
    *
    * It also depends on the fact that nodes at a lower depth have correct counts (which can be assured
    * by running the query at depth n+1).
    */
    "updateCountsForDepth" => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
    'query_string' => "
    UPDATE {TABLE_PREFIX}node AS target INNER JOIN
    (SELECT node.parentid AS nodeid,
    SUM(IF (node.contenttypeid NOT IN ({excluded}), 1, 0)) AS total,
    SUM(IF (node.showpublished AND node.showapproved AND node.contenttypeid NOT IN ({excluded}), 1, 0)) AS pubcount,
    SUM(node.totalcount) AS totalcount,
    SUM(node.totalunpubcount) AS totalunpubcount
    FROM {TABLE_PREFIX}node AS node INNER JOIN
    {TABLE_PREFIX}closure AS closure ON (node.parentid = closure.child AND depth = {depth})
    WHERE closure.parent = {rootnodeid}
    GROUP BY node.parentid
    ) AS sums ON (sums.nodeid = target.nodeid)
    SET target.textcount = sums.pubcount,
    target.textunpubcount = (sums.total - sums.pubcount),
    target.totalcount = sums.pubcount + sums.totalcount,
    target.totalunpubcount = (sums.total - sums.pubcount) + sums.totalunpubcount
    "
    ),






    Is there any reason why this query would take up to two minutes? I've attached the EXPLAIN statement from my DB, showing that it's checking EVERY row in the node table (we have over 18 millions nodes)



    Is it possible that I am missing some index on the node table, or that an index needs to be added there?



    Thanks





    Devami...
    Similar Threads
X

Thread Information

Collapse

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

    Those who read this thread

    Collapse

    Members who have read this thread: 0

      QUICK NAVIGATION

      Working...
      X

      AdBlock Detected

      Please Disable Adblock

      Please consider supporting us by disabling the ad blocker.

      I've Disabled AdBlock
      0x0