Page MenuHomePhabricator

updateArticleCount.inc.php can be made significantly more efficient
Closed, ResolvedPublic

Description

Author: sean

Description:
The query in updateArticleCount.inc.php's makeSql() function can be made faster an less RAM-intensive. It currently queries for a ton of rows, then counts the number of rows in the result.

The current query is:
return "SELECT DISTINCT page_namespace,page_title FROM $page,$pagelinks " .

			"WHERE pl_from=page_id and page_namespace IN ( $nsset ) " .
			"AND page_is_redirect = 0 AND page_len > 0";

but could be changed to:
return "SELECT COUNT(DISTINCT page_namespace,page_title) FROM $page,$pagelinks " .

			"WHERE pl_from=page_id and page_namespace IN ( $nsset ) " .
			"AND page_is_redirect = 0 AND page_len > 0";

The code in the count() function would have to be updated also. Instead of

			$count = $this->dbr->numRows( $res );
			$this->dbr->freeResult( $res );
			return $count;

it could be changed to:

			$row = $this->dbr->fetchObject( $res );
			$this->dbr->freeResult( $res );
			return (int)$row->count;

Version: unspecified
Severity: normal

Event Timeline

bzimport raised the priority of this task from to Medium.Nov 21 2014, 10:26 PM
bzimport set Reference to bz16632.
bzimport added a subscriber: Unknown Object (MLST).

sean wrote:

CORRECTION!!!

The query that it should be replaced with should be:
return "SELECT COUNT(DISTINCT page_namespace,page_title) as count FROM $page,$pagelinks" .

"WHERE pl_from=page_id and page_namespace IN ( $nsset )" .
"AND page_is_redirect = 0 AND page_len > 0";

The difference is the addition of " as count" in there. Otherwise there will be an error on the line below where it has "return (int)$row->count;"

Change 407650 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1100

https://gerrit.wikimedia.org/r/407650

Change 407650 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1100

https://gerrit.wikimedia.org/r/407650