Best way to purge old records from a huge table:
- Lock the old table
- Use INSERT SELECT to create a new table
- Drop the old table
- Rename the new table to the old table
- Unlock the old table
If I have to store URLs in a table, have to have guarantee the the URL is unique: I would probably add an extra an extra column which store an SHA1 / MD5 hash (or some other hash algorithm) of the URL, and specify the unique constraint on that column instead of specifying the unique constraint on the URL.
How to purposely crash a table?
- delete the .MYD or .MYI file (when deleting the MYD file, the table is still readable and writable, but doesn't have any info when selecting from it)
- Keep key_buffer_size very low and insert lots of data into table (myisam). After some time the index file will crash.
- dd if=table.MYI of=table2.MYI bs=2048 count=100 (do something like this in linux — assuming your table is not tiny, and mysql isn't running or you have a lock on the table) then copy table2.MYI over table.MYI and then "flush tables" and then unlock. Your table will be unreadable until you rebuild the index with REPAIR TABLE or myisamchk. The MYD file will remain intact. If your MYI file is smaller than 200k, then just reduce the count=#
- Open the data or index file, remove some text data in the file and save.