比如在表Login 中查询今天登陆了的用户,但是昨天也登陆了。
字段为
uid:用户账号 (不重复,用户唯一标识)
landtime:登陆时间现在我的语句是这样的:SELECT count(*) FROM Login WHERE uid IN
(SELECT uidfrom Login where landtime > '2011-7-21 00:00:00' and landtime < '2011-7-22 00:00:00')
and landtime > '2011-7-22 00:00:00' and landtime < '2011-7-23 00:00:00'
因为每天的数据都有3000-4000条左右,所以查询的速度非常慢400多秒,无法接受啊,求高手解惑!!
字段为
uid:用户账号 (不重复,用户唯一标识)
landtime:登陆时间现在我的语句是这样的:SELECT count(*) FROM Login WHERE uid IN
(SELECT uidfrom Login where landtime > '2011-7-21 00:00:00' and landtime < '2011-7-22 00:00:00')
and landtime > '2011-7-22 00:00:00' and landtime < '2011-7-23 00:00:00'
因为每天的数据都有3000-4000条左右,所以查询的速度非常慢400多秒,无法接受啊,求高手解惑!!
SELECT count(uid) FROM Login WHERE uid IN
(SELECT uid from Login where landtime > '2011-7-21 00:00:00' and landtime < '2011-7-22 00:00:00')
and landtime > '2011-7-22 00:00:00' and landtime < '2011-7-23 00:00:00'
二、建立索引建立索引后,可以看到明显效果
select count(*)
from(
select uid, count(*) as num from login
where landtime>'2011-07-21 00:00:00' and landtime<'2011-07-23 00:00:00'
group by uid,date(landtime)
having num>1
) as detail
不使用SELECT count(uid) 是快了一点点,但是还是需要很久,无法实现最终目的,
索引主要是不了解,没有用过。除了索引可否还有其他简便方法?
建索引:
create index inx on Login(landtime);查询:
SELECT distinct(uid) from Login where landtime > '2011-7-21 00:00:00' and landtime < '2011-7-22 00:00:00'
union
SELECT distinct(uid) from Login where landtime > '2011-7-22 00:00:00' and landtime < '2011-7-23 00:00:00';