r97998 MediaWiki - Code Review archive

Repository:MediaWiki
Revision:< r97997‎ | r97998 | r97999 >
Date:12:50, 24 September 2011
Author:catrope
Status:resolved (Comments)
Tags:
Comment:
MoodBar: Add mbf_id to the end of all indexes so we can do proper, indexed, sorted paging by timestamp and ID.
Modified paths:
  • /trunk/extensions/MoodBar/MoodBar.hooks.php (modified) (history)
  • /trunk/extensions/MoodBar/sql/AddIDToIndexes.sql (added) (history)
  • /trunk/extensions/MoodBar/sql/AddIDToIndexes2.sql (added) (history)
  • /trunk/extensions/MoodBar/sql/MoodBar.sql (modified) (history)
  • /trunk/extensions/MoodBar/sql/mbf_timestamp.sql (deleted) (history)
  • /trunk/extensions/MoodBar/sql/mbf_timestamp_id.sql (added) (history)

Diff [purge]

Index: trunk/extensions/MoodBar/sql/mbf_timestamp.sql
——@@ -1,5 +0,0 @@
2 -
3 -CREATE INDEX /*i*/mbf_userid_ip_timestamp ON /*_*/moodbar_feedback (mbf_user_id, mbf_user_ip, mbf_timestamp);
4 -CREATE INDEX /*i*/mbf_type_userid_ip_timestamp ON /*_*/moodbar_feedback (mbf_type, mbf_user_id, mbf_user_ip, mbf_timestamp);
5 -CREATE INDEX /*i*/mbf_timestamp ON /*_*/moodbar_feedback (mbf_timestamp);
Index: trunk/extensions/MoodBar/sql/MoodBar.sql
——@@ -26,9 +26,9 @@
2727 ) /*$wgDBTableOptions*/;
2828
2929 -- A little overboard with the indexes perhaps, but we want to be able to dice this data a lot!
30 -CREATE INDEX /*i*/type_timestamp ON /*_*/moodbar_feedback (mbf_type,mbf_timestamp);
31 -CREATE INDEX /*i*/title_type ON /*_*/moodbar_feedback (mbf_namespace,mbf_title,mbf_type,mbf_timestamp);
32 -CREATE INDEX /*i*/mbf_userid_ip_timestamp ON /*_*/moodbar_feedback (mbf_user_id, mbf_user_ip, mbf_timestamp);
33 -CREATE INDEX /*i*/mbf_type_userid_ip_timestamp ON /*_*/moodbar_feedback (mbf_type, mbf_user_id, mbf_user_ip, mbf_timestamp);
34 -CREATE INDEX /*i*/mbf_timestamp ON /*_*/moodbar_feedback (mbf_timestamp);
 30+CREATE INDEX /*i*/mbf_type_timestamp_id ON /*_*/moodbar_feedback (mbf_type,mbf_timestamp, mbf_id);
 31+CREATE INDEX /*i*/mbf_title_type_id ON /*_*/moodbar_feedback (mbf_namespace,mbf_title,mbf_type,mbf_timestamp, mbf_id);
 32+-- CREATE INDEX /*i*/mbf_namespace_title_timestamp ON /*_*/moodbar_feedback (mbf_namespace, mbf_title, mbf_timestamp, mbf_id); --maybe in the future if we actually do per-page filtering
 33+CREATE INDEX /*i*/mbf_userid_ip_timestamp_id ON /*_*/moodbar_feedback (mbf_user_id, mbf_user_ip, mbf_timestamp, mbf_id);
 34+CREATE INDEX /*i*/mbf_type_userid_ip_timestamp_id ON /*_*/moodbar_feedback (mbf_type, mbf_user_id, mbf_user_ip, mbf_timestamp, mbf_id);
 35+CREATE INDEX /*i*/mbf_timestamp_id ON /*_*/moodbar_feedback (mbf_timestamp, mbf_id);
Index: trunk/extensions/MoodBar/sql/mbf_timestamp_id.sql
——@@ -0,0 +1,5 @@
 2+-- Add some indexes to the moodbar_feedback table
 3+
 4+CREATE INDEX /*i*/mbf_userid_ip_timestamp_id ON /*_*/moodbar_feedback (mbf_user_id, mbf_user_ip, mbf_timestamp, mbf_id);
 5+CREATE INDEX /*i*/mbf_type_userid_ip_timestamp_id ON /*_*/moodbar_feedback (mbf_type, mbf_user_id, mbf_user_ip, mbf_timestamp, mbf_id);
 6+CREATE INDEX /*i*/mbf_timestamp_id ON /*_*/moodbar_feedback (mbf_timestamp, mbf_id);
Property changes on: trunk/extensions/MoodBar/sql/mbf_timestamp_id.sql
___________________________________________________________________
Added: svn:eol-style
17 + native
Index: trunk/extensions/MoodBar/sql/AddIDToIndexes.sql
——@@ -0,0 +1,5 @@
 2+-- Drop and recreate indexes to add mbf_id to the end
 3+DROP INDEX /*i*/type_timestamp ON /*_*/moodbar_feedback;
 4+DROP INDEX /*i*/title_type ON /*_*/moodbar_feedback;
 5+CREATE INDEX /*i*/mbf_type_timestamp_id ON /*_*/moodbar_feedback (mbf_type,mbf_timestamp, mbf_id);
 6+CREATE INDEX /*i*/mbf_title_type_id ON /*_*/moodbar_feedback (mbf_namespace,mbf_title,mbf_type,mbf_timestamp, mbf_id);
\ No newline at end of file
Property changes on: trunk/extensions/MoodBar/sql/AddIDToIndexes.sql
___________________________________________________________________
Added: svn:eol-style
17 + native
Index: trunk/extensions/MoodBar/sql/AddIDToIndexes2.sql
——@@ -0,0 +1,5 @@
 2+-- Drop and recreate indexes to add mbf_id to the end
 3+DROP INDEX /*i*/mbf_userid_ip_timestamp ON /*_*/moodbar_feedback;
 4+DROP INDEX /*i*/mbf_type_userid_ip_timestamp ON /*_*/moodbar_feedback;
 5+DROP INDEX /*i*/mbf_timestamp ON /*_*/moodbar_feedback;
 6+-- Recreation is done in mbf_timestamp_id.sql
Property changes on: trunk/extensions/MoodBar/sql/AddIDToIndexes2.sql
___________________________________________________________________
Added: svn:eol-style
17 + native
Index: trunk/extensions/MoodBar/MoodBar.hooks.php
——@@ -75,8 +75,18 @@
7676 'mbf_user_editcount', dirname(__FILE__).'/sql/mbf_user_editcount.sql', true )
7777 );
7878
 79+ $db = $updater->getDB();
 80+ if ( $db->indexExists( 'moodbar_feedback', 'type_timestamp', __METHOD__ ) ) {
 81+ $updater->addExtensionUpdate( array( 'addIndex', 'moodbar_feedback',
 82+ 'mbf_type_timestamp_id', dirname( __FILE__ ) . '/sql/AddIDToIndexes.sql', true )
 83+ );
 84+ }
 85+ $updater->addExtensionUpdate( array( 'dropIndex', 'moodbar_feedback',
 86+ 'mbf_timestamp', dirname( __FILE__ ) . '/sql/AddIDToIndexes2.sql', true )
 87+ );
 88+
7989 $updater->addExtensionUpdate( array( 'addIndex', 'moodbar_feedback',
80 - 'mbf_timestamp', dirname( __FILE__ ) . '/sql/mbf_timestamp.sql', true )
 90+ 'mbf_timestamp_id', dirname( __FILE__ ) . '/sql/mbf_timestamp_id.sql', true )
8191 );
8292
8393 return true;

Follow-up revisions

RevisionCommit summaryAuthorDate
r97999Use the indexes added in r97998 to do ORDER BY mbf_timestamp, mbf_idcatrope12:56, 24 September 2011
r98042Fix bug in r97998: updates are deferred, so indexExists() failed with a nonex...catrope23:03, 24 September 2011

Comments

#Comment by Krinkle (talk | contribs)   23:06, 24 September 2011 
The last attempted database query was:
"SHOW INDEX FROM `moodbar_feedback`"
from within function "MoodBarHooks::onLoadExtensionSchemaUpdates". Database returned error "1146: Table 'mediawiki.moodbar_feedback' doesn't exist (127.0.0.1)"

Table doesn't exist for existing installs. Fixed in r98042.

Status & tagging log