Duplicate Records
Here’s a very cool thing I just discovered in MySQL. Duplicate records in a db are the bane of the software developer. If you have the opportunity to generate the original table schema and set up the primary keys beforehand, that’s great, but if you’re given a table after the fact and are asked to deal with duplicate records, it’s a real headache. You end up shuffling records around, trying to create new keys and if you’re really lucky, the table is huge with six indexes and any modifications you try to make hang everything. If you’re extra lucky, you’re dealing with realtime production data that’s constantly being written to as you’re trying to clean up the data. Check this out:
ALTER IGNORE TABLE mytable ADD UNIQUE (key1,key2)
This doesn’t add or remove any records, it just tells MySQL to ignore records with a duplicate composite key whenever you do selects. Pretty cool. This probably isn’t a long term solution but gets you out of an immediate crisis. You can then erase the problematic records on your own time.
