[MediaWiki-commits] [Gerrit] Improve use of index for fetching most recent feedback activ... - change (mediawiki...ArticleFeedbackv5)
jenkins-bot has submitted this change and it was merged.
Change subject: Improve use of index for fetching most recent feedback activity
from logging table
..
Improve use of index for fetching most recent feedback activity from logging
table
Previous code (on current enwiki.logging)
+--+-+-+--+---+---+-+---+-+--+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows| Extra|
+--+-+-+--+---+---+-+---+-+--+
|1 | SIMPLE | logging | ref | page_time | page_time | 4 |
const | 1330612 | Using where; Using temporary; Using filesort |
+--+-+-+--+---+---+-+---+-+--+
This patch (on current enwiki.logging)
+--+-+-+---+---+---+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra
|
+--+-+-+---+---+---+-+--+--+-+
|1 | SIMPLE | logging | range | page_time | page_time | 261 |
NULL | 76 | Using index condition; Using where; Using temporary; Using
filesort |
+--+-+-+---+---+---+-+--+--+-+
Change-Id: Ib6755276a561a30bee1feadd9df5a8442b1ef6e8
---
M ArticleFeedbackv5.activity.php
1 file changed, 18 insertions(+), 9 deletions(-)
Approvals:
Lwelling: Looks good to me, approved
jenkins-bot: Verified
diff --git a/ArticleFeedbackv5.activity.php b/ArticleFeedbackv5.activity.php
index d872dc3..39cc937 100644
--- a/ArticleFeedbackv5.activity.php
+++ b/ArticleFeedbackv5.activity.php
@@ -380,6 +380,7 @@
$activity = array();
$where = array();
+ $titles = array();
// build where-clause for all feedback entries
foreach ( $entries as $entry ) {
@@ -418,6 +419,7 @@
$actions = self::buildWhereActions( array(),
$actions );
if ( $actions ) {
$title = self::buildWhereFeedback(
$feedback );
+ $titles[] = $title;
$where[] = 'log_title =
'.$dbr->addQuotes( $title ).' AND '.$actions;
}
}
@@ -425,8 +427,23 @@
// if there are entries not found in cache, fetch them from DB
if ( $where ) {
+ $options = array();
+
+ // specific conditions to find the exact action we're
looking for, per page
$where = array( '('.implode( ') OR (', $where ).')' );
+ $options['GROUP BY'] = array( 'log_namespace',
'log_title' );
+
+ /*
+* Even though log_title is already in the above
where-conditions (to find
+* specific actions per title), we'll add these again
to target index
+* page_time (on _namespace, _title, _timestamp). This
will result in very
+* few remaining columns (all logging data for maximum
+* ArticleFeedbackv5Model::LIST_LIMIT pages), which can
then easily be
+* scanned using WHERE.
+*/
$where['log_namespace'] = NS_SPECIAL;
+ $where['log_title'] = $titles;
+ $options['USE INDEX'] = 'page_time';
/*
* The goal is to fetch only the last (editor) action
for every feedback
@@ -439,15 +456,7 @@
array( 'last_id' => 'MAX(log_id)' ),
$where,
__METHOD__,
- array(
- 'GROUP BY' => array( 'log_namespace',
'log_title' ),
- // Force the page_time index (on
_namespace, _title, _timestamp)
- // We don't expect many if any rows for
Special:ArticleFeedbackv5/foo that
- // don't match
log_type='articlefeedbackv5' , s
[MediaWiki-commits] [Gerrit] Improve use of index for fetching most recent feedback activ... - change (mediawiki...ArticleFeedbackv5)
Matthias Mullie has uploaded a new change for review.
https://gerrit.wikimedia.org/r/60227
Change subject: Improve use of index for fetching most recent feedback activity
from logging table
..
Improve use of index for fetching most recent feedback activity from logging
table
Previous code (on current enwiki.logging)
+--+-+-+--+---+---+-+---+-+--+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows| Extra|
+--+-+-+--+---+---+-+---+-+--+
|1 | SIMPLE | logging | ref | page_time | page_time | 4 |
const | 1330612 | Using where; Using temporary; Using filesort |
+--+-+-+--+---+---+-+---+-+--+
This patch (on current enwiki.logging)
+--+-+-+---+---+---+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra
|
+--+-+-+---+---+---+-+--+--+-+
|1 | SIMPLE | logging | range | page_time | page_time | 261 |
NULL | 76 | Using index condition; Using where; Using temporary; Using
filesort |
+--+-+-+---+---+---+-+--+--+-+
Change-Id: Ib6755276a561a30bee1feadd9df5a8442b1ef6e8
---
M ArticleFeedbackv5.activity.php
1 file changed, 18 insertions(+), 9 deletions(-)
git pull
ssh://gerrit.wikimedia.org:29418/mediawiki/extensions/ArticleFeedbackv5
refs/changes/27/60227/1
diff --git a/ArticleFeedbackv5.activity.php b/ArticleFeedbackv5.activity.php
index d872dc3..39cc937 100644
--- a/ArticleFeedbackv5.activity.php
+++ b/ArticleFeedbackv5.activity.php
@@ -380,6 +380,7 @@
$activity = array();
$where = array();
+ $titles = array();
// build where-clause for all feedback entries
foreach ( $entries as $entry ) {
@@ -418,6 +419,7 @@
$actions = self::buildWhereActions( array(),
$actions );
if ( $actions ) {
$title = self::buildWhereFeedback(
$feedback );
+ $titles[] = $title;
$where[] = 'log_title =
'.$dbr->addQuotes( $title ).' AND '.$actions;
}
}
@@ -425,8 +427,23 @@
// if there are entries not found in cache, fetch them from DB
if ( $where ) {
+ $options = array();
+
+ // specific conditions to find the exact action we're
looking for, per page
$where = array( '('.implode( ') OR (', $where ).')' );
+ $options['GROUP BY'] = array( 'log_namespace',
'log_title' );
+
+ /*
+* Even though log_title is already in the above
where-conditions (to find
+* specific actions per title), we'll add these again
to target index
+* page_time (on _namespace, _title, _timestamp). This
will result in very
+* few remaining columns (all logging data for maximum
+* ArticleFeedbackv5Model::LIST_LIMIT pages), which can
then easily be
+* scanned using WHERE.
+*/
$where['log_namespace'] = NS_SPECIAL;
+ $where['log_title'] = $titles;
+ $options['USE INDEX'] = 'page_time';
/*
* The goal is to fetch only the last (editor) action
for every feedback
@@ -439,15 +456,7 @@
array( 'last_id' => 'MAX(log_id)' ),
$where,
__METHOD__,
- array(
- 'GROUP BY' => array( 'log_namespace',
'log_title' ),
- // Force the page_time index (on
_namespace, _title, _timestamp)
- // We don't expect many if any rows for
Special:ArticleFeedbackv5/foo that
-
