Catrope has uploaded a new change for review.
https://gerrit.wikimedia.org/r/252614
Change subject: Add FORCE INDEX to contributions queries to work around
optimizer bug
......................................................................
Add FORCE INDEX to contributions queries to work around optimizer bug
With an index on (rev_user_id, rev_user_ip, rev_user_wiki) and a primary
key on (rev_id), queries like WHERE rev_user_id=X AND rev_user_ip=Y
AND rev_user_wiki=Z ORDER BY rev_id DESC work fine, but adding
AND rev_id < 'W' causes MySQL to do an index merge and a filesort.
Adding FORCE INDEX fixes this.
Not adding this for the newbie case, because that query doesn't
use the same pattern, and flow_revision_user may not be the
right index there.
Bug: T78671
Change-Id: Ib83275d4cdfc272cd0a532fbbb2675bdfae1ce6d
---
M includes/Formatter/ContributionsQuery.php
1 file changed, 20 insertions(+), 17 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/mediawiki/extensions/Flow
refs/changes/14/252614/1
diff --git a/includes/Formatter/ContributionsQuery.php
b/includes/Formatter/ContributionsQuery.php
index 972813d..7e1280a 100644
--- a/includes/Formatter/ContributionsQuery.php
+++ b/includes/Formatter/ContributionsQuery.php
@@ -62,8 +62,8 @@
* @return FormatterRow[]
*/
public function getResults( $pager, $offset, $limit, $descending ) {
- // build DB query conditions
- $conditions = $this->buildConditions( $pager, $offset,
$descending );
+ // build DB query conditions and options
+ $queryInfo = $this->buildQueryInfo( $pager, $offset,
$descending );
$types = array(
// revision class => block type
@@ -75,7 +75,7 @@
$results = array();
foreach ( $types as $revisionClass => $blockType ) {
// query DB for requested revisions
- $rows = $this->queryRevisions( $conditions, $limit,
$revisionClass );
+ $rows = $this->queryRevisions( $queryInfo, $limit,
$revisionClass );
if ( !$rows ) {
continue;
}
@@ -128,10 +128,11 @@
* @param ContribsPager|DeletedContribsPager $pager Object hooked into
* @param string $offset Index offset, inclusive
* @param bool $descending Query direction, false for ascending, true
for descending
- * @return array Query conditions
+ * @return array Query conditions and options
*/
- protected function buildConditions( $pager, $offset, $descending ) {
+ protected function buildQueryInfo( $pager, $offset, $descending ) {
$conditions = array();
+ $options = array();
// Work out user condition
if ( property_exists( $pager, 'contribs' ) && $pager->contribs
== 'newbie' ) {
@@ -156,6 +157,8 @@
$conditions['rev_user_ip'] = $pager->target;
$conditions['rev_user_wiki'] = wfWikiId();
}
+ // Hack around optimizer bug (T78671): index not used
correctly when $offset is set
+ $options['USE INDEX']['flow_revision'] =
'flow_revision_user';
}
// Make offset parameter.
@@ -172,17 +175,17 @@
$conditions['workflow_namespace'] = $pager->namespace;
}
- return $conditions;
+ return array( 'conditions' => $conditions, 'options' =>
$options );
}
/**
- * @param array $conditions
+ * @param array $queryInfo
* @param int $limit
* @param string $revisionClass Storage type (e.g. "PostRevision",
"Header")
* @return ResultWrapper|false false on failure
* @throws \MWException
*/
- protected function queryRevisions( $conditions, $limit, $revisionClass
) {
+ protected function queryRevisions( $queryInfo, $limit, $revisionClass )
{
$dbr = $this->dbFactory->getDB( DB_SLAVE );
switch ( $revisionClass ) {
@@ -195,12 +198,12 @@
'flow_workflow', // resolve to
workflow, to test if in correct wiki/namespace
),
array( '*' ),
- $conditions,
+ $queryInfo['conditions'],
__METHOD__,
- array(
+ array_merge( $queryInfo['options'],
array(
'LIMIT' => $limit,
'ORDER BY' => 'rev_id DESC',
- ),
+ ) ),
array(
'flow_tree_revision' => array(
'INNER JOIN',
@@ -226,12 +229,12 @@
return $dbr->select(
array( 'flow_revision', 'flow_workflow'
),
array( '*' ),
- $conditions,
+ $queryInfo['conditions'],
__METHOD__,
- array(
+ array_merge( $queryInfo['options'],
array(
'LIMIT' => $limit,
'ORDER BY' => 'rev_id DESC',
- ),
+ ) ),
array(
'flow_workflow' => array(
'INNER JOIN',
@@ -245,16 +248,16 @@
return $dbr->select(
array( 'flow_revision',
'flow_workflow', 'flow_tree_node' ),
array( '*' ),
- array_merge( $conditions, array(
+ array_merge( $queryInfo['conditions'],
array(
'workflow_id =
tree_ancestor_id',
'tree_descendant_id =
rev_type_id',
'rev_type' => 'post-summary'
) ),
__METHOD__,
- array(
+ array_merge( $queryInfo['options'],
array(
'LIMIT' => $limit,
'ORDER BY' => 'rev_id DESC',
- )
+ ) )
);
break;
--
To view, visit https://gerrit.wikimedia.org/r/252614
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Ib83275d4cdfc272cd0a532fbbb2675bdfae1ce6d
Gerrit-PatchSet: 1
Gerrit-Project: mediawiki/extensions/Flow
Gerrit-Branch: master
Gerrit-Owner: Catrope <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
