ApiQueryLogEvents: Add IGNORE INDEX to avoid MariaDB optimizer bug

The optimizer in MariaDB 10.1.37 and 10.1.38 seems to wildly
underestimate the cost of using the `times` index and overestimate the
cost of using the `user_time` or `actor_time` indexes, leading to it
choosing a plan that may have to fetch and filesort 10000000s of rows
versus the 10s or 1000s fetched with no filesort if it used the correct
index.

We can get it to use the correct index by having it ignore `times`, and
this also won't interfere with it choosing `log_user_type_time`,
`log_actor_type_time`, or other more specific indexes in cases where
those would be more appropriate.

As usual, this should be reviewed as MySQL/MariaDB versions used are
updated and removed if the optimizer is fixed.

Bug: T71222
Change-Id: I16dba2158865917e763722a5848cb5d483bf1a0b

diff --git a/includes/api/ApiQueryLogEvents.php b/includes/api/ApiQueryLogEvents.php
index 0934ab3..7d74c07 100644 (file)
--- a/includes/api/ApiQueryLogEvents.php
+++ b/includes/api/ApiQueryLogEvents.php
@@ -183,6 +183,10 @@ class ApiQueryLogEvents extends ApiQueryBase {
                                $db, 'log_user', User::newFromName( $params['user'], false )
                        );
                        $this->addWhere( $q['conds'] );
+
+                       // T71222: MariaDB's optimizer, at least 10.1.37 and .38, likes to choose a wildly bad plan for
+                       // some reason for this code path. Tell it not to use the wrong index it wants to pick.
+                       $this->addOption( 'IGNORE INDEX', [ 'logging' => [ 'times' ] ] );
                }
 
                $title = $params['title'];