MessageCache: Add STRAIGHT_JOIN to avoid planner oddness

For some unknown reason, when the `actor` table has few enough NS8 rows
compared to `page` MariaDB 10.1.37 decides it makes more sense to fetch
everything from `actor` then join `revision` then `page` rather than
fetching the rows from `page` in the first place.

We can work around it by telling it to not reorder the query, but then
we also have to reorder it ourselves to put `page` first instead of
`revision`.

Bug: T231196
Change-Id: I2b2fb209e648d1e407c5c2d32d3ac9e574e361d5

diff --git a/includes/cache/MessageCache.php b/includes/cache/MessageCache.php
index 93fdb16..86a2785 100644 (file)
--- a/includes/cache/MessageCache.php
+++ b/includes/cache/MessageCache.php
@@ -504,6 +504,21 @@ class MessageCache {
                // Set the text for small software-defined messages in the main cache map
                $revisionStore = MediaWikiServices::getInstance()->getRevisionStore();
                $revQuery = $revisionStore->getQueryInfo( [ 'page', 'user' ] );
+
+               // T231196: MySQL/MariaDB (10.1.37) can sometimes irrationally decide that querying `actor` then
+               // `revision` then `page` is somehow better than starting with `page`. Tell it not to reorder the
+               // query (and also reorder it ourselves because as generated by RevisionStore it'll have
+               // `revision` first rather than `page`).
+               $revQuery['joins']['revision'] = $revQuery['joins']['page'];
+               unset( $revQuery['joins']['page'] );
+               // It isn't actually necesssary to reorder $revQuery['tables'] as Database does the right thing
+               // when join conditions are given for all joins, but Gergő is wary of relying on that so pull
+               // `page` to the start.
+               $revQuery['tables'] = array_merge(
+                       [ 'page' ],
+                       array_diff( $revQuery['tables'], [ 'page' ] )
+               );
+
                $res = $dbr->select(
                        $revQuery['tables'],
                        $revQuery['fields'],
@@ -512,7 +527,7 @@ class MessageCache {
                                'page_latest = rev_id' // get the latest revision only
                        ] ),
                        __METHOD__ . "($code)-small",
-                       [],
+                       [ 'STRAIGHT_JOIN' ],
                        $revQuery['joins']
                );
                foreach ( $res as $row ) {