如何优化联合查询这样两个表？

A表（排班表）
usrID time1 time2  
123 2011-12-19 08:00:00 2011-12-19 18:00:00
456 2011-12-19 08:00:00 2011-12-19 18:00:00
123 2011-12-20 08:00:00 2011-12-20 18:00:00
B表（进出时间表）
userID time
123 2011-12-19 08:12:01
123 2011-12-19 08:30:01
456 2011-12-19 08:16:01

B表有上万记录，A表固定上千条记录

select B.*,
  (select max(A.time) from A where A.userID = B.userID and A.time between B.time1 and B.time2) as acttime
from B


这样查询花费时间太久，如何优化？
 
  

a:userID time
b:userID time1 time2
建立复合索引

SQL code

select B.userid,B.time,max(A.time)
from A,B
where A.userID = B.userID and A.time between B.time1 and B.time2
group by B.userid,B.time

B表你反正是全面扫描，有没有索引对效率不起作用。添加A表如下索引即可。

create index xxx on A ( userID,time)