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 <mmul...@wikimedia.org> _______________________________________________ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits