关于in和exists的优化问题，搜索无准确结果，在MYSQL

Question

如下搜索要62.75S，时间太久
select count(ID) from baggoodsinlog where intime>='2011-05-23 00:00:00' and intime <'2011-05-31 00:00:00' and inmode=6 and goodsid in (34410001,34410002,34410003,34410004,34410005,34410006,34410007,34410008,34410009,34410010,34410011,34410012,34410013,34410014,34410015,34410016);尝试改成如下，无效，老是提示syntax to use near'34410001,34410002.....：select a.goodsid from(select id,goodsid from usr_baggoodsinlog where intime>='2011-05-23 00:00:00' and intime <'2011-05-31 00:00:00' and inmode=6)  a where  exists (34410001,34410002,34410003,34410004,34410005,34410006,34410007,34410008,34410009,34410010,34410011,34410012,34410013,34410014,34410015,34410016);会的请写一下吧，看在我结贴率100%。

Answer 1

在intime、goodsid 上建立复合索引试试

Answer 2

数据表结构不是我能改动。现有索引   KEY `idx_user` (`UserID`,`GoodsID`) USING BTREE,
  KEY `idx_goods` (`GoodsID`),

Answer 3

第一个语句 
select count(ID) from baggoodsinlog 
where intime>='2011-05-23 00:00:00' and intime <'2011-05-31 00:00:00' 
and inmode=6 
and goodsid in (34410001,34410002,34410003,34410004,34410005,34410006,34410007,34410008,34410009,34410010,34410011,34410012,34410013,34410014,34410015,34410016);你只有KEY `idx_goods` (`GoodsID`),这个索引可用，如果速度很慢，则没有办法，除非可以添加 intime，inmode的索引。

Answer 4

第二个语句，则根本语法不对，不知道你想实现什么逻辑功能，所以也无法帮你修改。select a.goodsid from (
select id,goodsid from usr_baggoodsinlog where intime>='2011-05-23 00:00:00' and intime <'2011-05-31 00:00:00' and inmode=6
)  a 
where  exists (34410001,34410002,34410003,34410004,34410005,34410006,34410007,34410008,34410009,34410010,34410011,34410012,34410013,34410014,34410015,34410016);正常的EXISTS用法select * from t where exists (select * from b where id=t.id)

Answer 5

explain下看是否用到了goodsid 索引试试强制索引
select count(ID) from baggoodsinlog force index(idx_goods) where intime>='2011-05-23 00:00:00' and intime <'2011-05-31 00:00:00' and inmode=6 and goodsid in (34410001,34410002,34410003,34410004,34410005,34410006,34410007,34410008,34410009,34410010,34410011,34410012,34410013,34410014,34410015,34410016);

Answer 6

to ACMAIN_CHM
    索引确实只有2个，我无法去改表。
第二个语句的逻辑应该很明显。
select a.goodsid from (
select id,goodsid from usr_baggoodsinlog where intime>='2011-05-23 00:00:00' and intime <'2011-05-31 00:00:00' and inmode=6
) a  
where exists (34410001,34410002,34410003,34410004,34410005,34410006,34410007,34410008,34410009,34410010,34410011,34410012,34410013,34410014,34410015,34410016);按照你的select * from t where exists (select * from b where id=t.id)那我的就是可以select * from a where exists (那些数字)就是从usr_baggoodsinlog搜索Goodsid,然后这些goodsid exists(那些数字），如果用IN很方便，但是时间很长，所以我想改用exists但是我改来改去不行。