From 80b9ad3a8a356220223033103252df4ff6e31839 Mon Sep 17 00:00:00 2001
From: Jcrespo <jcrespo@wikimedia.org>
Date: Wed, 8 Mar 2017 09:10:19 +0000
Subject: [PATCH] Change Title::getPreviousRevisionID (and next) to ignore
 PRIMARY

Both Title::getPreviousRevisionID and Title::getNextRevisionID
have bad performance under certain versions of MySQL/MariaDB
when the page has many revisions (in the order of dozens of
thousands). This is good enough in most cases.

However, on a contributions slave, where it has an explicity
defined extended secondary key with the primary key, the
performance is really, really bad, performing a full table scan.

By ignoring the PRIMARY KEY index, contributions slaves go from the
worst case to the best case, while not affecting the plan of regular
slaves. It is believed that more recent versions of MySQL chose the
right indexes automatically, because they can use the extended primary
key automatically, without adding it explicitly. If that doesn't
happen, we can consider adding it explicitly to the regular slaves,
too.

In any case, not using the PRIMARY gives always a much better or at
least the same performance (this never causes a regression, unlike
FORCE'ing a specific index).

Bug: T159319
Change-Id: Ibb6e5240b87bd8e2d680fc4d58fcb3db1a4721cc
---
 includes/Title.php | 20 ++++++++++++++++----
 1 file changed, 16 insertions(+), 4 deletions(-)

diff --git a/includes/Title.php b/includes/Title.php
index 3ed6b8bb6e..1ddb2fce7c 100644
--- a/includes/Title.php
+++ b/includes/Title.php
@@ -3963,14 +3963,22 @@ class Title implements LinkTarget {
 	 * @return int|bool Old revision ID, or false if none exists
 	 */
 	public function getPreviousRevisionID( $revId, $flags = 0 ) {
-		$db = ( $flags & self::GAID_FOR_UPDATE ) ? wfGetDB( DB_MASTER ) : wfGetDB( DB_REPLICA );
+		/* This function and getNextRevisionID have bad performance when
+		   used on a page with many revisions on mysql. An explicit extended
+		   primary key may help in some cases, if the PRIMARY KEY is banned:
+		   T159319 */
+		if ( $flags & self::GAID_FOR_UPDATE ) {
+			$db = wfGetDB( DB_MASTER );
+		} else {
+			$db = wfGetDB( DB_REPLICA, 'contributions' );
+		}
 		$revId = $db->selectField( 'revision', 'rev_id',
 			[
 				'rev_page' => $this->getArticleID( $flags ),
 				'rev_id < ' . intval( $revId )
 			],
 			__METHOD__,
-			[ 'ORDER BY' => 'rev_id DESC' ]
+			[ 'ORDER BY' => 'rev_id DESC', 'IGNORE INDEX' => 'PRIMARY' ]
 		);
 
 		if ( $revId === false ) {
@@ -3988,14 +3996,18 @@ class Title implements LinkTarget {
 	 * @return int|bool Next revision ID, or false if none exists
 	 */
 	public function getNextRevisionID( $revId, $flags = 0 ) {
-		$db = ( $flags & self::GAID_FOR_UPDATE ) ? wfGetDB( DB_MASTER ) : wfGetDB( DB_REPLICA );
+		if ( $flags & self::GAID_FOR_UPDATE ) {
+			$db = wfGetDB( DB_MASTER );
+		} else {
+			$db = wfGetDB( DB_REPLICA, 'contributions' );
+		}
 		$revId = $db->selectField( 'revision', 'rev_id',
 			[
 				'rev_page' => $this->getArticleID( $flags ),
 				'rev_id > ' . intval( $revId )
 			],
 			__METHOD__,
-			[ 'ORDER BY' => 'rev_id' ]
+			[ 'ORDER BY' => 'rev_id', 'IGNORE INDEX' => 'PRIMARY' ]
 		);
 
 		if ( $revId === false ) {
-- 
2.20.1