开发者

exclude many users in mysql

thank you very much in advance for the help. I have to make an exclusion of users, the problem is that it takes too long to do (about 5 hours or more) In my database I have about 800,000 users of which I exclude around 580000 users (I get the 220000 users that are not repeated).

  1. to make this a first method:

SELECT iduser FROM userstotal WHERE iduser NOT IN( SELECT iduser FROM users220000)

(This is a simplified example of what I do but that's t开发者_如何转开发he idea) This takes about 5 hours or more to perform the query.

I was thinking of a php script to add in an array of users and then go excluding repeated.

That you advise me to optimize the SQL query or some optimum method for this.

  • For reference use MyISAM


I would rewrite the query as

SELECT   ut.iduser
FROM     userstotal ut
WHERE    NOT EXISTS (
           SELECT  u2.iduser 
           FROM    users220000 u2 
           WHERE   u2.iduser = ut.iduser
         )

and make sure that iduser of the userstotal table is indexed.

I doubt an index on users220000 would make any difference but it wouldn't hurt either.

edit

Thanks to Ronnis for spotting the missing clause.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜