FAQTs

Home My Faqts Contributors About Help

Entry

Add Entry Alert - Edit this Entry

Did You Find This Entry Useful?

0 of 1 people (0%) answered Yes
Recently 0 of 1 people (0%) answered Yes

What? Creating an index can remove duplicates?

Jun 23rd, 2004 23:52

Mike Chirico
http://sourceforge.net/project/showfiles.php?group_id=79320&package;_id=115696

Remove duplicate entires.  Assume the following table and data.
              CREATE TABLE IF NOT EXISTS dupTest (          
                pkey int(11) NOT NULL auto_increment,        
                a int,                                       
                b int,                                       
                c int,                                       
                timeEnter timestamp(14),                     
               PRIMARY KEY  (pkey)                           
               );
               insert into dupTest (a,b,c) values (1,2,3),(1,2,3),
                  (1,5,4),(1,6,4);
Note, the first two rows contains duplicates in columns a and b. It 
contains other duplicates; but, the following index leaves the other 
duplicates alone.
          mysql> ALTER IGNORE TABLE  dupTest ADD UNIQUE INDEX(a,b);
For more detail on this example see TIP 5, from the source URL.