r86527 MediaWiki - Code Review archive

Repository:MediaWiki
Revision:< r86526‎ | r86527 | r86528 >
Date:19:02, 20 April 2011
Author:happy-melon
Status:resolved (Comments)
Tags:
Comment:
Follow-up r86485: SQL error in patch, and also add the index to tables.sql.
Modified paths:
  • /trunk/phase3/maintenance/archives/patch-user_email_index.sql (modified) (history)
  • /trunk/phase3/maintenance/tables.sql (modified) (history)

Diff [purge]

Index: trunk/phase3/maintenance/archives/patch-user_email_index.sql
——@@ -1 +1 @@
2 - ALTER TABLE /*$wgDBprefix*/user ADD INDEX ( `user_email`, `user_name` );
\ No newline at end of file
 2+ ALTER TABLE /*$wgDBprefix*/user ADD INDEX ( `user_email`(50), `user_name` );
\ No newline at end of file
Index: trunk/phase3/maintenance/tables.sql
——@@ -135,6 +135,7 @@
136136
137137 CREATE UNIQUE INDEX /*i*/user_name ON /*_*/user (user_name);
138138 CREATE INDEX /*i*/user_email_token ON /*_*/user (user_email_token);
 139+CREATE INDEX /*i*/user_email ON /*_*/user (user_email(50),user_name);
139140
140141
141142 --

Follow-up revisions

RevisionCommit summaryAuthorDate
r92932Follow-up r86482, r86485: removed user_name portion of user_email index per CRaaron08:15, 23 July 2011

Past revisions this follows-up on

RevisionCommit summaryAuthorDate
r86485Follow-up r86482: patch to add an index on user_email. Not a problem if this...happy-melon15:33, 20 April 2011

Comments

#Comment by Catrope (talk | contribs)   08:19, 21 April 2011

The query is still not indexed right. I don't think there's any way that MySQL lets you properly index a BLOB or TEXT field. I'm not sure how bad this is, though, but maybe $wgPasswordResetRoutes['email'] should be documented as possibly being slow on wikis with many users.

#Comment by MZMcBride (talk | contribs)   20:44, 21 April 2011

I'm not sure about "properly," but according to the docs (<http://dev.mysql.com/doc/refman/5.0/en/blob.html> and <http://dev.mysql.com/doc/refman/5.0/en/column-indexes.html>), it should be possible to index any MySQL data type, though you must specify an index prefix length when indexing blob or text columns.

#Comment by Catrope (talk | contribs)   22:10, 21 April 2011

By "properly" I meant "does what you want". I know that partial indexes (you can't have full indexes on blobs, only partial ones) aren't used for resolving ORDER BYs, not even partially, but I'm not entirely sure how this works for WHERE clauses.

#Comment by Aaron Schulz (talk | contribs)   18:54, 30 June 2011

The 'user_name' part of the INDEX won't be useful here due to the nature of the 'user_email' portion.

#Comment by Aaron Schulz (talk | contribs)   08:05, 23 July 2011

Well it could avoid a secondary lookup for most email addys (<=50 chars), but this isn't really a common operation, so I'm not sure if it's worth the second index portion.

Status & tagging log