'Better MySQL Query

I have a small email newsletter system and when a new email address is added (through mass import) it defaults to "subscribed int(11) DEFAULT '1'`".

I then have the below query which looks for the email address and updates any that are already in the table but are un-subscribed :

UPDATE emailData SET subscribed =  '0'
WHERE subscribed = '1' and emailAddress IN

(
  SELECT emailAddress FROM
  (SELECT emailAddress FROM emailData  WHERE subscribed = '0' GROUP BY emailAddress) AS tmptable

)

With around 5000 duplicates it takes around 15 seconds to execute (VM Server) and I wanted to know if there was a better / faster way to do this?

mysqlsubquery


Solution 1:[1]

Yes, there's a better way - don't have duplicates. Create a unique (primary?) index on email address and set your import operation to ignore duplicates (otherwise it will still do the import but generate lots of errors).

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 symcbean

Related Questions