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

如下搜索要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%。

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

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

第一个语句 
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的索引。

第二个语句，则根本语法不对，不知道你想实现什么逻辑功能，所以也无法帮你修改。

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)