SELECT DISTINCT
(b.nid),
b.*,
t.tid ttid,
t.targetid ttargetid,
t.mid tmid,
t.type ttype,
t.subject tsubject,
t.content tcontent,
t.reviewnum treviewnum,
t.goodnum tgoodnum,
t.photopath tphotopath
FROM tour_timeline_notice b
JOIN tour_timeline_notice a
ON b.targetid = a.targetid
JOIN tour_timeline_target t
ON b.tid = t.tid
WHERE b.isOwnerJoin = 1
AND b.type = a.type
AND b.action = a.action
AND b.createtime > a.createtime
AND a.replymid = '011BF005520A1C7EFF8080'
AND (a.action = 'r'
OR a.action = 'j')
AND b.replymid != '011BF005520A1C7EFF8080'
AND (b.action = 'r'
OR b.action = 'j')
AND t.mid != '011BF005520A1C7EFF8080'
AND b.createtime > DATE_SUB(CURDATE(), INTERVAL 2 MONTH)
AND a.createtime > DATE_SUB(CURDATE(), INTERVAL 2 MONTH)
ORDER BY b.createtime DESC
LIMIT 100
CREATE TABLE `tour_timeline_notice` (
`nid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`mid` char(32) CHARACTER SET ascii NOT NULL COMMENT '动作人mid',
`tid` int(10) unsigned NOT NULL COMMENT 'tour_timeline_target主键',
`targetid` char(32) CHARACTER SET ascii NOT NULL COMMENT '目标对象id',
`action` char(1) CHARACTER SET ascii NOT NULL,
`type` char(1) CHARACTER SET ascii NOT NULL,
`replyid` char(32) CHARACTER SET ascii DEFAULT NULL,
`replymid` char(32) CHARACTER SET ascii DEFAULT NULL COMMENT '回复者mid',
`replyComment` varchar(2000) DEFAULT NULL COMMENT '回复内容',
`createtime` datetime NOT NULL,
`isOwnerJoin` tinyint(1) DEFAULT '0' COMMENT '是否是“我参与的”类型,“0”:不是,“1”:是',
PRIMARY KEY (`nid`),
KEY `replymid` (`replymid`),
KEY `tid` (`tid`),
KEY `mid_createtime` (`mid`,`createtime`),
KEY `targetid` (`targetid`),
KEY `replyid` (`replyid`),
KEY `NewIndex1` (`createtime`)
) ENGINE=InnoDB AUTO_INCREMENT=5734453 DEFAULT CHARSET=utf8 COMMENT='我的空间动态'
;CREATE TABLE `tour_timeline_target` (
`tid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`targetid` char(32) CHARACTER SET ascii NOT NULL COMMENT '目标对象id',
`mid` char(32) CHARACTER SET ascii NOT NULL COMMENT '目标对象创建用户id',
`type` char(1) CHARACTER SET ascii NOT NULL,
`subject` varchar(128) DEFAULT NULL,
`content` varchar(2000) DEFAULT NULL,
`reviewnum` smallint(4) DEFAULT '0',
`goodnum` mediumint(8) DEFAULT '0',
`photopath` varchar(1536) CHARACTER SET ascii DEFAULT NULL,
PRIMARY KEY (`tid`),
UNIQUE KEY `targetid_type` (`targetid`,`type`),
KEY `mid` (`mid`)
) ENGINE=InnoDB AUTO_INCREMENT=8969824 DEFAULT CHARSET=utf8 COMMENT='空间动态内容表'
;
(b.nid),
b.*,
t.tid ttid,
t.targetid ttargetid,
t.mid tmid,
t.type ttype,
t.subject tsubject,
t.content tcontent,
t.reviewnum treviewnum,
t.goodnum tgoodnum,
t.photopath tphotopath
FROM tour_timeline_notice b
JOIN tour_timeline_notice a
ON b.targetid = a.targetid
JOIN tour_timeline_target t
ON b.tid = t.tid
WHERE b.isOwnerJoin = 1
AND b.type = a.type
AND b.action = a.action
AND b.createtime > a.createtime
AND a.replymid = '011BF005520A1C7EFF8080'
AND (a.action = 'r'
OR a.action = 'j')
AND b.replymid != '011BF005520A1C7EFF8080'
AND (b.action = 'r'
OR b.action = 'j')
AND t.mid != '011BF005520A1C7EFF8080'
AND b.createtime > DATE_SUB(CURDATE(), INTERVAL 2 MONTH)
AND a.createtime > DATE_SUB(CURDATE(), INTERVAL 2 MONTH)
ORDER BY b.createtime DESC
LIMIT 100
CREATE TABLE `tour_timeline_notice` (
`nid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`mid` char(32) CHARACTER SET ascii NOT NULL COMMENT '动作人mid',
`tid` int(10) unsigned NOT NULL COMMENT 'tour_timeline_target主键',
`targetid` char(32) CHARACTER SET ascii NOT NULL COMMENT '目标对象id',
`action` char(1) CHARACTER SET ascii NOT NULL,
`type` char(1) CHARACTER SET ascii NOT NULL,
`replyid` char(32) CHARACTER SET ascii DEFAULT NULL,
`replymid` char(32) CHARACTER SET ascii DEFAULT NULL COMMENT '回复者mid',
`replyComment` varchar(2000) DEFAULT NULL COMMENT '回复内容',
`createtime` datetime NOT NULL,
`isOwnerJoin` tinyint(1) DEFAULT '0' COMMENT '是否是“我参与的”类型,“0”:不是,“1”:是',
PRIMARY KEY (`nid`),
KEY `replymid` (`replymid`),
KEY `tid` (`tid`),
KEY `mid_createtime` (`mid`,`createtime`),
KEY `targetid` (`targetid`),
KEY `replyid` (`replyid`),
KEY `NewIndex1` (`createtime`)
) ENGINE=InnoDB AUTO_INCREMENT=5734453 DEFAULT CHARSET=utf8 COMMENT='我的空间动态'
;CREATE TABLE `tour_timeline_target` (
`tid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`targetid` char(32) CHARACTER SET ascii NOT NULL COMMENT '目标对象id',
`mid` char(32) CHARACTER SET ascii NOT NULL COMMENT '目标对象创建用户id',
`type` char(1) CHARACTER SET ascii NOT NULL,
`subject` varchar(128) DEFAULT NULL,
`content` varchar(2000) DEFAULT NULL,
`reviewnum` smallint(4) DEFAULT '0',
`goodnum` mediumint(8) DEFAULT '0',
`photopath` varchar(1536) CHARACTER SET ascii DEFAULT NULL,
PRIMARY KEY (`tid`),
UNIQUE KEY `targetid_type` (`targetid`,`type`),
KEY `mid` (`mid`)
) ENGINE=InnoDB AUTO_INCREMENT=8969824 DEFAULT CHARSET=utf8 COMMENT='空间动态内容表'
;
解决方案 »
- mysql 执行存储过程报错
- MySQL关于学生表、科目表、成绩表,三表联合查询学生成绩表,科目表随时可能改变
- 请问,win7下如何安装MYSQL4
- 数据库系统概论作业: 求至少用了供应商 Sl 所供应的全部零件的工程号 JNO ;
- postgres 的存储过程 请高手帮忙看看 急急急
- 求一条sql语句(有关复合查询,唯一记录)。弄一晚上了,都写不出来,大哥大姐帮帮忙。
- MYSQL的一个简单语法
- 数据库如何根据两个字段进行分表
- 江湖救急.........Mysql多服务器搭建问题,请个大神指导一下
- 求助一下高手们,这样的视图怎么建立呢。
- 请教在控制台上执行source命令的问题
- 多游戏排名,好难啊,求大神们帮忙
比如tour_timeline_notice
isOwnerJoin、type、action、createtime 、replymid 建立复合索引
create index xxxx2 on tour_timeline_notice(targetid,type,action,replymid,createtime);
create index xxxx11 on tour_timeline_notice(replymid,createtime);
create index xxxx21 on tour_timeline_notice(targetid,type,action,isOwnerJoin,createtime);