有一个员工属性表,有上百万的数据,需要查询出每个员工特定时间最新的数据并过滤,sql执行太慢,
select * from employe_info where id in ( select max(id) from employe group by employe_id where date < '2016-01-01') e
where level = 1 limit 10
select * from employe_info where id in ( select max(id) from employe group by employe_id where date < '2016-01-01') e
where level = 1 limit 10
解决方案 »
- [真伪]数据库中 select count(1) 比 select count(*) 快?
- 避免重复插入数据,又要保持高效率
- maysql 视图问题 请教
- 有那位高手知道为什么用select * from table_name where birth="841204" 和select count(*) from table_name where birth="841204"他们显示的记录条数不同 急!!!!
- 做一个百货公司的网站,正考虑使用哪种数据库,请帮帮忙。
- 保存路径问题
- java 操作Mysql问题……
- mysql中如何查询某个数据库中所有的自定义索引
- mysql cluster往表中插入数据会自动分区存储到不同数据节点吗
- 【求助】mysql查询三个表
- 请问,像这种多条件查询的该怎么建立索引?
- sql语句实现
a.列名
from employe_info a
left join (
select max(id) id from employe group by employe_id where date < '2016-01-01'
)e on a.id=b.id
where level=1
你试试这个写法看看。
explain select ...
show index from ..
以供分析。
另一方面,你的外层有限制条件 where level = 1 ,这个如果能够大量过滤数据的话,应该考虑在 group by 前也加上这个条件
2 SUBQUERY employe_info2 index id_key id_key 10 1608626 Using where
CREATE TABLE `employe` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `employe_info` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`employe_id` int(10) DEFAULT NULL,
`level` int(2) DEFAULT NULL,
`date` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_employe` (`employe_id`),
KEY `date_index` (`date`) USING BTREE,
CONSTRAINT `fk_employe` FOREIGN KEY (`employe_id`) REFERENCES `employe` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO employe (name, address) VALUES ('joe', 'joe address');INSERT INTO employe (name, address) VALUES ('mads', 'mads address');INSERT INTO employe (name, address) VALUES ('max', 'max address');INSERT INTO employe_info (employe_id, level, date) VALUES ('1', '1', '2016-01-01');INSERT INTO employe_info (employe_id, level, date) VALUES ('2', '1', '2016-01-02');INSERT INTO employe_info (employe_id, level, date) VALUES ('3', '1', '2016-01-03');INSERT INTO employe_info (employe_id, level, date) VALUES ('1', '2', '2015-01-01');INSERT INTO employe_info (employe_id, level, date) VALUES ('2', '3', '2015-10-02');INSERT INTO employe_info (employe_id, level, date) VALUES ('3', '4', '2015-08-03');INSERT INTO employe_info (employe_id, level, date) VALUES ('1', '6', '2015-06-01');INSERT INTO employe_info (employe_id, level, date) VALUES ('2', '2', '2015-09-02');INSERT INTO employe_info (employe_id, level, date) VALUES ('3', '4', '2015-06-03');INSERT INTO employe_info (employe_id, level, date) VALUES ('1', '1', '2015-07-01');INSERT INTO employe_info (employe_id, level, date) VALUES ('2', '1', '2015-10-02');INSERT INTO employe_info (employe_id, level, date) VALUES ('3', '1', '2015-11-03');查询语句:select * from employe_info where id in ( select max(id) from employe_info where date < '2016-02-01' group by employe_id ) and level = 1 limit 10