maintenance/sqlite/archives/patch-change_tag-change_tag_rc_tag_id.sql

   1 -- T193874: Add new indexes to change_tag table using ct_tag_id instead of ct_tag
   2 BEGIN TRANSACTION;
   3
   4 DROP TABLE IF EXISTS /*_*/change_tag_tmp;
   5
   6 CREATE TABLE /*_*/change_tag_tmp (
   7   ct_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
   8   ct_rc_id int NULL,
   9   ct_log_id int unsigned NULL,
  10   ct_rev_id int unsigned NULL,
  11   ct_tag varchar(255) NOT NULL default '',
  12   ct_params blob NULL,
  13   ct_tag_id int unsigned NULL
  14 ) /*$wgDBTableOptions*/ MAX_ROWS=10000000 AVG_ROW_LENGTH=1024;
  15
  16 INSERT OR IGNORE INTO /*_*/change_tag_tmp (ct_id, ct_rc_id, ct_log_id, ct_rev_id, ct_tag, ct_params, ct_tag_id)
  17     SELECT
  18     ct_id, ct_rc_id, ct_log_id, ct_rev_id, ct_tag, ct_params, ct_tag_id
  19     FROM /*_*/change_tag;
  20
  21 DROP TABLE /*_*/change_tag;
  22
  23 ALTER TABLE /*_*/change_tag_tmp RENAME TO /*_*/change_tag;
  24
  25 CREATE INDEX /*i*/change_tag_rc_tag_nonuniq ON /*_*/change_tag (ct_rc_id,ct_tag);
  26 CREATE INDEX /*i*/change_tag_log_tag_nonuniq ON /*_*/change_tag (ct_log_id,ct_tag);
  27 CREATE INDEX /*i*/change_tag_rev_tag_nonuniq ON /*_*/change_tag (ct_rev_id,ct_tag);
  28 CREATE UNIQUE INDEX /*i*/change_tag_rc_tag_id ON /*_*/change_tag (ct_rc_id,ct_tag_id);
  29 CREATE UNIQUE INDEX /*i*/change_tag_log_tag_id ON /*_*/change_tag (ct_log_id,ct_tag_id);
  30 CREATE UNIQUE INDEX /*i*/change_tag_rev_tag_id ON /*_*/change_tag (ct_rev_id,ct_tag_id);
  31 CREATE INDEX /*i*/change_tag_tag_id ON /*_*/change_tag (ct_tag,ct_rc_id,ct_rev_id,ct_log_id);
  32 CREATE INDEX /*i*/change_tag_tag_id_id ON /*_*/change_tag (ct_tag_id,ct_rc_id,ct_rev_id,ct_log_id);
  33
  34 COMMIT;