在开发过程中遇到的问题:
有两张表,登录记录表和IP归属表
需求:查询某个地区和某个时间段登录的记录
有登录表有4000条数据,IP归属表有400条数据
用GROUP BY查询结果
SELECT * FROM login_log WHERE acctLoginIP IN (SELECT ip FROM ip_geolocation WHERE
region LIKE '%西藏%' GROUP BY ip) AND acctLoginTime BETWEEN '2014-10-01 00:00:00' AND '2014-12-25 23:59:59' LIMIT 10;
结果用时4秒左右
用DISTINCT
SELECT * FROM login_log WHERE acctLoginIP IN (SELECT DISTINCT ip FROM ip_geolocation WHERE
region LIKE '%西藏%') AND acctLoginTime BETWEEN '2014-10-01 00:00:00' AND '2014-12-25 23:59:59' LIMIT 10;
结果用时0.005秒左右
有登录表有4000条数据