同表更新

Question

大家好,请教个问题场景:在用户信息表中 根据注册ip（regist_ip）判断是否为新ip(标记)
条件：新的regist_ip不在同表已有的regist_ip内表:b_player_stat_info
UPDATE 
  `b_player_stat_info` 
SET
  `is_new_ip` = 1 
WHERE regist_time BETWEEN '2013-01-16 17:00:00' 
  AND '2013-01-16 17:59:59' 
  AND `regist_ip` NOT IN 
  (SELECT 
    regist_ip 
  FROM
    (SELECT 
      `regist_ip` 
    FROM
      `b_player_stat_info` 
    WHERE `regist_time` < '2013-01-16 17:00:00') AS b)
执行效率很低 如何优化？

Answer 1

UPDATE   `b_player_stat_info`
SET  `is_new_ip` = 1
WHERE regist_time BETWEEN '2013-01-16 17:00:00' AND '2013-01-16 17:59:59'
  AND exists(SELECT `regist_ip` FROM `b_player_stat_info`
              WHERE `regist_time` < '2013-01-16 17:00:00')增加 regist_time 和 regist_ip的索引会更快

Answer 2

谢谢amoyman 那样写逻辑有点错误用exists我是这样写的update 
  `b_player_stat_info` a 
set
  a.`is_new_ip` = 1 
where a.regist_time BETWEEN '2013-01-16 17:00:00' 
  AND '2013-01-16 17:59:59' 
  and not exists 
  (select 
    1 
  from
    `b_player_stat_info` b 
  where b.`regist_time` < '2013-01-16 17:00:00' 
    and a.`regist_ip` = b.`regist_ip`)但是跑了半天没执行完用户表大概190万行 用第一种要跑16分钟51秒 这个是正常范围内的吗？有没有更好的优化方案？

Answer 3

update `b_player_stat_info` A,(SELECT 
      `regist_ip` 
    FROM
      `b_player_stat_info` 
    WHERE `regist_time` < '2013-01-16 17:00:00') B
set A.`is_new_ip` = 1 
where A.regist_time BETWEEN '2013-01-16 17:00:00' 
  AND '2013-01-16 17:59:59' 
  AND A.`regist_ip` <> B.regist_ip A.regist_time加索引

Answer 4

desc SELECT       `regist_ip`     FROM      `b_player_stat_info`     WHERE `regist_time` < '2013-01-16 17:00:00';看看这个子查询的效率怎么样？如果不行，在regist_ip，regist_time字段加索引。

Answer 5

#rucypli谢谢加了索引, 按你的语句跑了一个多小时还没跑完 估计也是有问题.

Answer 6

select * from `b_player_stat_info`
WHERE regist_time BETWEEN '2013-01-16 17:00:00'  AND '2013-01-16 17:59:59' 
需要多长时间
UPDATE `b_player_stat_info` a LEFT JOIN `b_player_stat_info` b
  ON a.`regist_ip`=b.`regist_ip`
  SET  a.`is_new_ip` = 1 
WHERE a.regist_time BETWEEN '2013-01-16 17:00:00'  AND '2013-01-16 17:59:59' 
AND b.regist_time < '2013-01-16 17:00:00'
AND b.`regist_ip` IS NULL
regist_time建立索引没有，is_new_ip有几种值，2种就没有必要建立索引了