[Summary view] [Print] [Text view]

   1  -- patch-random-dateindex.sql
   2  -- 2003-02-09
   3  --
   4  -- This patch does two things:
   5  --  * Adds cur_random column to replace random table
   6  --    (Requires change to SpecialRandom.php)
   7  --    random table no longer needs refilling
   8  --    Note: short-term duplicate results *are* possible, but very unlikely on large wiki
   9  --
  10  --  * Adds inverse_timestamp columns to cur and old and indexes
  11  --    to allow descending timestamp sort in history, contribs, etc
  12  --    (Requires changes to Article.php, DatabaseFunctions.php,
  13  --     ... )
  14  --                       cur_timestamp  inverse_timestamp
  15  --     99999999999999 - 20030209222556 = 79969790777443
  16  --     99999999999999 - 20030211083412 = 79969788916587
  17  --
  18  --    We won't need this on MySQL 4; there will be a removal patch later.
  19  
  20  -- Indexes:
  21  -- cur needs (cur_random) for random sort
  22  -- cur and old need (namespace,title,timestamp) index for history,watchlist,rclinked
  23  -- cur and old need (user,timestamp) index for contribs
  24  -- cur and old need (user_text,timestamp) index for contribs
  25  
  26  ALTER TABLE /*$wgDBprefix*/cur
  27    DROP INDEX cur_user,
  28    DROP INDEX cur_user_text,
  29    ADD COLUMN cur_random real unsigned NOT NULL,
  30    ADD COLUMN inverse_timestamp char(14) binary NOT NULL default '',
  31    ADD INDEX (cur_random),
  32    ADD INDEX name_title_timestamp (cur_namespace,cur_title,inverse_timestamp),
  33    ADD INDEX user_timestamp (cur_user,inverse_timestamp),
  34    ADD INDEX usertext_timestamp (cur_user_text,inverse_timestamp);
  35  
  36  UPDATE /*$wgDBprefix*/cur SET
  37    inverse_timestamp=99999999999999-cur_timestamp,
  38    cur_random=RAND();
  39  
  40  ALTER TABLE /*$wgDBprefix*/old
  41    DROP INDEX old_user,
  42    DROP INDEX old_user_text,
  43    ADD COLUMN inverse_timestamp char(14) binary NOT NULL default '',
  44    ADD INDEX name_title_timestamp (old_namespace,old_title,inverse_timestamp),
  45    ADD INDEX user_timestamp (old_user,inverse_timestamp),
  46    ADD INDEX usertext_timestamp (old_user_text,inverse_timestamp);
  47  
  48  UPDATE /*$wgDBprefix*/old SET
  49    inverse_timestamp=99999999999999-old_timestamp;
  50  
  51  -- If leaving wiki publicly accessible in read-only mode during
  52  -- the upgrade, comment out the below line; leave 'random' table
  53  -- in place until the new software is installed.
  54  DROP TABLE /*$wgDBprefix*/random;