苦労して書いたmysqlマルチフィールドフィルタ重量sql

              user_reply reply_mention    user_reply_mention,      rid uid     ，    ,     ，   sql  ，        。

CREATE TABLE `user_reply_mention` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '  ID',
  `rid` int(11) DEFAULT NULL COMMENT '    ID',
  `uid` int(11) DEFAULT NULL COMMENT '  ID',
  `type` int(2) DEFAULT NULL COMMENT '    (0      1      2      ',
  `create_time` datetime DEFAULT NULL COMMENT '    ',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_rid_uid` (`rid`,`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=3000000 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='       '; 



CREATE TABLE `user_reply_mention2` (
  `id` int(11) unsigned NULL AUTO_INCREMENT COMMENT '  ID',
  `rid` int(11) DEFAULT NULL COMMENT '    ID',
  `uid` int(11) DEFAULT NULL COMMENT '  ID',
  `type` int(2) DEFAULT NULL COMMENT '    (0      1      2      ',
  `create_time` datetime DEFAULT NULL COMMENT '    ',
  `rid_uid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_rid_uid` (`rid_uid`(191))
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='       2';

CREATE TABLE `user_reply_mention3` (
  `id` int(11) DEFAULT  NULL COMMENT '  ID',
  `rid` int(11) DEFAULT NULL COMMENT '    ID',
  `uid` int(11) DEFAULT NULL COMMENT '  ID',
  `type` int(2) DEFAULT NULL COMMENT '    (0      1      2      ',
  `create_time` datetime DEFAULT NULL COMMENT '    ',
  `rid_uid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  KEY `rid_uid` (`rid_uid`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='       3';


CREATE TABLE `user_reply_mention4` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '  ID',
  `rid` int(11) DEFAULT NULL COMMENT '    ID',
  `uid` int(11) DEFAULT NULL COMMENT '  ID',
  `type` int(2) DEFAULT NULL COMMENT '    (0      1      2      ',
  `create_time` datetime DEFAULT NULL COMMENT '    ',
  `rid_uid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `rid_uid` (`rid_uid`(191))
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='       3';


#    
alter table user_reply_mention2 auto_increment=0; 
insert into user_reply_mention2(id,uid,rid,create_time,type) select id,uid,rid,createtime,1 as type from user_reply; 

alter table user_reply_mention2 auto_increment=2000000;
insert into user_reply_mention2(uid,rid,create_time,type) select uid,rid,create_time,2 as type from reply_mention;
alter table user_reply_mention2 auto_increment=3000000;


update user_reply_mention2 set rid_uid = concat(rid,'-',uid);

#    
insert into user_reply_mention3(rid_uid) select A.rid_uid from  ((select count(id) co, rid_uid  from user_reply_mention2 group by rid_uid having co > 1) A); //133439

update user_reply_mention3 B,user_reply_mention2 A set B.id = A.id where B.rid_uid = A.rid_uid;
#    
select sum(A.co) from ((select count(1) as  co, rid_uid  from user_reply_mention2 group by rid_uid having co > 1)A);//266893
select count(B.id) from user_reply_mention3 B,user_reply_mention2 A  where B.rid_uid = A.rid_uid and A.id != B.id; // 133454(    )
#user_reply_mention2    4
insert into user_reply_mention4(id,uid,rid,create_time,type,rid_uid) select id,uid,rid,create_time,type,rid_uid from user_reply_mention2; 
#  2     

select count(*) from user_reply_mention4   where id in (select A.id from user_reply_mention2 A,user_reply_mention3 B where A.rid_uid = B.rid_uid and A.id != B.id); //133454(    )

delete from user_reply_mention4   where id in (select A.id from user_reply_mention2 A,user_reply_mention3 B where A.rid_uid = B.rid_uid and A.id != B.id); //133454(    )

#         
select count(id) co, rid_uid  from user_reply_mention4 group by rid_uid having co > 1; //0 
# 4  
 2   4  
insert into user_reply_mention(id,uid,rid,create_time,type) select id,uid,rid,create_time,type from user_reply_mention2; //1661812
#  
delete from user_reply_mention where id < 3000000;
#   4  
insert into user_reply_mention(id,uid,rid,create_time,type) select id,uid,rid,create_time,type from user_reply_mention4; //1528358

#  

#        

select id,uid,rid,createtime,1 as type from user_reply where id > 1776557;

select max(id) from user_reply_mention2;1776557   id(type 1)
2147792(type 2)
insert into user_reply_mention(id,uid,rid,create_time,type) select id,uid,rid,createtime,1 as type from user_reply where id > 1776557; 
1790896
1924350(  )