From 11cf19d3ff0335706378b0b9f3d69091e4241f23 Mon Sep 17 00:00:00 2001
From: Brad Jorsch <bjorsch@wikimedia.org>
Date: Fri, 4 Sep 2015 16:24:54 -0400
Subject: [PATCH] ApiQueryDeletedRevisions: Optimize ascending title generation

When generating titles rather than revids, it would be nice if we could
query with DISTINCT to get the titles directly instead of processing
every deleted revision for the title.

Sadly, it turns out this only works with dir=newer (dir=older is the
default). But that's probably better than nothing.

Bug: T110792
Change-Id: Idd300510c92b00722574d046399dbeb486afa98b
---
 includes/api/ApiQueryAllDeletedRevisions.php | 44 +++++++++++++++++---
 1 file changed, 39 insertions(+), 5 deletions(-)

diff --git a/includes/api/ApiQueryAllDeletedRevisions.php b/includes/api/ApiQueryAllDeletedRevisions.php
index 4e4d2af73d..f802c9569d 100644
--- a/includes/api/ApiQueryAllDeletedRevisions.php
+++ b/includes/api/ApiQueryAllDeletedRevisions.php
@@ -83,6 +83,21 @@ class ApiQueryAllDeletedRevisions extends ApiQueryRevisionsBase {
 			}
 		}
 
+		// If we're generating titles only, we can use DISTINCT for a better
+		// query. But we can't do that in 'user' mode (wrong index), and we can
+		// only do it when sorting ASC (because MySQL apparently can't use an
+		// index backwards for grouping even though it can for ORDER BY, WTF?)
+		$dir = $params['dir'];
+		$optimizeGenerateTitles = false;
+		if ( $mode === 'all' && $params['generatetitles'] && $resultPageSet !== null ) {
+			if ( $dir === 'newer' ) {
+				$optimizeGenerateTitles = true;
+			} else {
+				$p = $this->getModulePrefix();
+				$this->setWarning( "For better performance when generating titles, set {$p}dir=newer" );
+			}
+		}
+
 		$this->addTables( 'archive' );
 		if ( $resultPageSet === null ) {
 			$this->parseParameters( $params );
@@ -90,7 +105,12 @@ class ApiQueryAllDeletedRevisions extends ApiQueryRevisionsBase {
 			$this->addFields( array( 'ar_title', 'ar_namespace' ) );
 		} else {
 			$this->limit = $this->getParameter( 'limit' ) ?: 10;
-			$this->addFields( array( 'ar_title', 'ar_namespace', 'ar_timestamp', 'ar_rev_id', 'ar_id' ) );
+			$this->addFields( array( 'ar_title', 'ar_namespace' ) );
+			if ( $optimizeGenerateTitles ) {
+				$this->addOption( 'DISTINCT' );
+			} else {
+				$this->addFields( array( 'ar_timestamp', 'ar_rev_id', 'ar_id' ) );
+			}
 		}
 
 		if ( $this->fld_tags ) {
@@ -130,7 +150,6 @@ class ApiQueryAllDeletedRevisions extends ApiQueryRevisionsBase {
 			}
 		}
 
-		$dir = $params['dir'];
 		$miser_ns = null;
 
 		if ( $mode == 'all' ) {
@@ -229,7 +248,14 @@ class ApiQueryAllDeletedRevisions extends ApiQueryRevisionsBase {
 		if ( !is_null( $params['continue'] ) ) {
 			$cont = explode( '|', $params['continue'] );
 			$op = ( $dir == 'newer' ? '>' : '<' );
-			if ( $mode == 'all' ) {
+			if ( $optimizeGenerateTitles ) {
+				$this->dieContinueUsageIf( count( $cont ) != 2 );
+				$ns = intval( $cont[0] );
+				$this->dieContinueUsageIf( strval( $ns ) !== $cont[0] );
+				$title = $db->addQuotes( $cont[1] );
+				$this->addWhere( "ar_namespace $op $ns OR " .
+					"(ar_namespace = $ns AND ar_title $op= $title)" );
+			} elseif ( $mode == 'all' ) {
 				$this->dieContinueUsageIf( count( $cont ) != 4 );
 				$ns = intval( $cont[0] );
 				$this->dieContinueUsageIf( strval( $ns ) !== $cont[0] );
@@ -259,7 +285,13 @@ class ApiQueryAllDeletedRevisions extends ApiQueryRevisionsBase {
 
 		$sort = ( $dir == 'newer' ? '' : ' DESC' );
 		$orderby = array();
-		if ( $mode == 'all' ) {
+		if ( $optimizeGenerateTitles ) {
+			// Targeting index name_title_timestamp
+			if ( $params['namespace'] === null || count( array_unique( $params['namespace'] ) ) > 1 ) {
+				$orderby[] = "ar_namespace $sort";
+			}
+			$orderby[] = "ar_title $sort";
+		} elseif ( $mode == 'all' ) {
 			// Targeting index name_title_timestamp
 			if ( $params['namespace'] === null || count( array_unique( $params['namespace'] ) ) > 1 ) {
 				$orderby[] = "ar_namespace $sort";
@@ -283,7 +315,9 @@ class ApiQueryAllDeletedRevisions extends ApiQueryRevisionsBase {
 		foreach ( $res as $row ) {
 			if ( ++$count > $this->limit ) {
 				// We've had enough
-				if ( $mode == 'all' ) {
+				if ( $optimizeGenerateTitles ) {
+					$this->setContinueEnumParameter( 'continue', "$row->ar_namespace|$row->ar_title" );
+				} elseif ( $mode == 'all' ) {
 					$this->setContinueEnumParameter( 'continue',
 						"$row->ar_namespace|$row->ar_title|$row->ar_timestamp|$row->ar_id"
 					);
-- 
2.20.1