select f.channel, count(1)
from flight_ticket_order f
inner join user u
on f.user_id = u.id
where f.booking_time between 20100928000000 and 20120928235959
and f.pay_state in (2, 5, 9, 10)
and not exists (select fto.user_id from flight_ticket_order fto
where fto.pay_state in (2, 5, 9, 10) and fto.booking_time < 20100928000000 and fto.user_id = f.user_id)
group by f.channel
语句还能怎么优化
from flight_ticket_order f
inner join user u
on f.user_id = u.id
where f.booking_time between 20100928000000 and 20120928235959
and f.pay_state in (2, 5, 9, 10)
and not exists (select fto.user_id from flight_ticket_order fto
where fto.pay_state in (2, 5, 9, 10) and fto.booking_time < 20100928000000 and fto.user_id = f.user_id)
group by f.channel
语句还能怎么优化
在user_id、pay_state、booking_time上建立索引没有
create index x2 on flight_ticket_order (pay_state,booking_time);
create index x3 on user (id);
如果仍慢,则贴出你的explain select f.channel, count(1)
from flight_ticket_order f inner join user u on f.user_id = u.id
where f.booking_time between 20100928000000 and 20120928235959
and f.pay_state in (2, 5, 9, 10)
and not exists (
select fto.user_id
from flight_ticket_order fto
where fto.pay_state in (2, 5, 9, 10)
and fto.booking_time < 20100928000000
and fto.user_id = f.user_id
)
group by f.channel;以供分析。