From e6021abc9c1654c09d3b2ae9b4b4d723fd43c983 Mon Sep 17 00:00:00 2001 From: Brad Jorsch Date: Mon, 4 Mar 2019 10:44:05 -0500 Subject: [PATCH] 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 --- includes/api/ApiQueryLogEvents.php | 4 ++++ 1 file changed, 4 insertions(+) diff --git a/includes/api/ApiQueryLogEvents.php b/includes/api/ApiQueryLogEvents.php index 0934ab391d..7d74c07b8e 100644 --- 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']; -- 2.20.1