试试这个 SELECT * FROM t_pj_schedulelx s inner join ( SELECT id FROM t_pj_schedulelx WHERE (state='6' OR state='11' OR state='15') AND (source_type='15' OR source_type='16') GROUP BY source_id,source_type HAVING COUNT(id) > 1 ) t on s.id = t.id WHERE ((OWNER = '8a8a8aed46d609e10146d612d37e0004' AND s.state = '6' AND s.update_Time IS NOT NULL) OR (s.state='11' AND s.update_Time IS NOT NULL) OR (s.state='15' AND s.update_Time IS NOT NULL)) AND s.create_Time >= '2014-10-26 00:00:00' AND s.create_Time <= '2014-11-02 23:59:59' ORDER BY s.classes, s.project_Num+0 ASC;
及 show index from ..
以供分析。
建表语句:
CREATE TABLE `t_pj_schedulelx` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`owner` varchar(32) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`state` varchar(8) DEFAULT NULL,
`source_id` bigint(20) DEFAULT NULL,
`source_name` varchar(64) DEFAULT NULL,
`source_type` varchar(8) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`content` varchar(200) DEFAULT NULL,
`classes` varchar(255) DEFAULT NULL,
`project_num` varchar(20) DEFAULT NULL,
`pro_role` varchar(32) DEFAULT NULL,
`last` varchar(32) DEFAULT NULL,
`is_yun_nan` varchar(2000) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2593 DEFAULT CHARSET=utf8我对state、source_type做过索引 没什么效果,谢谢 求分析
建表语句:
CREATE TABLE `t_pj_schedulelx` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`owner` varchar(32) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`state` varchar(8) DEFAULT NULL,
`source_id` bigint(20) DEFAULT NULL,
`source_name` varchar(64) DEFAULT NULL,
`source_type` varchar(8) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`content` varchar(200) DEFAULT NULL,
`classes` varchar(255) DEFAULT NULL,
`project_num` varchar(20) DEFAULT NULL,
`pro_role` varchar(32) DEFAULT NULL,
`last` varchar(32) DEFAULT NULL,
`is_yun_nan` varchar(2000) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2593 DEFAULT CHARSET=utf8我对state、source_type做过索引 没什么效果,谢谢 求分析这是一个事务表,里面包含了评分和投票的记录,我需要查询的就是自己的评分和所有项目的投票(但是只取项目其中的一条投票记录)
SELECT * FROM t_pj_schedulelx s
inner join (
SELECT id FROM t_pj_schedulelx
WHERE (state='6' OR state='11' OR state='15')
AND (source_type='15' OR source_type='16')
GROUP BY source_id,source_type HAVING COUNT(id) > 1
) t
on s.id = t.id
WHERE ((OWNER = '8a8a8aed46d609e10146d612d37e0004' AND s.state = '6' AND s.update_Time IS NOT NULL)
OR (s.state='11' AND s.update_Time IS NOT NULL)
OR (s.state='15' AND s.update_Time IS NOT NULL))
AND s.create_Time >= '2014-10-26 00:00:00' AND s.create_Time <= '2014-11-02 23:59:59'
ORDER BY s.classes, s.project_Num+0 ASC;