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

diff --git a/includes/Title.php b/includes/Title.php
index 3ed6b8b..1ddb2fc 100644 (file)
--- 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 ) {