Tweet
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...
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...
