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
- // don't match
log_type='articlefeedbackv5' , so we can afford to have that
- // clause be unindexed. The alternative
is to have the log_type clause be indexed
- // and the namespace/title clauses
unindexed, that would be bad.
- 'USE INDEX' => 'page_time'
- )
+ $options
);
$rows = ArticleFeedbackv5Utils::getDB( DB_SLAVE
)->select(
--
To view, visit https://gerrit.wikimedia.org/r/60227
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Ib6755276a561a30bee1feadd9df5a8442b1ef6e8
Gerrit-PatchSet: 1
Gerrit-Project: mediawiki/extensions/ArticleFeedbackv5
Gerrit-Branch: master
Gerrit-Owner: Matthias Mullie <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
