Though I do not know what your schema is, I think you may try to decompose it into several BCNFs which can erase the redundancy of emailaddress. ----- Original Message ----- From: "Marcus Bointon" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Cc: "Chris" <[EMAIL PROTECTED]> Sent: Tuesday, May 09, 2006 8:12 PM Subject: Re: Case confusion
> On 9 May 2006, at 02:22, Chris wrote: > >> I would run this query: >> >> SELECT >> * >> FROM mytable >> WHERE LOWER(emailaddress) IN >> (SELECT >> LOWER(emailaddress) >> FROM mytable >> GROUP BY 1 >> HAVING COUNT(emailaddress) > 1) >> >> This would show all duplicate emails, I would use the info this >> displays to choose which records to change/keep/delete. >> >> May not be the best way, but it would work. > > I'm giving it a go, but it's been running for about 4 hours at 60% > CPU so far! Would it be quicker to tell it to use a case insensitive > collation to locate the duplicates? > > Marcus > -- > Marcus Bointon > Synchromedia Limited: Putting you in the picture > [EMAIL PROTECTED] | http://www.synchromedia.co.uk
