Tip & Tricks

Use TRUNCATE TABLE to Start Fresh

After doing some research I found out that when you truncate a MySQL table, you are actually dropping the database table and re-creating the table from its SQL create statement.  This has one major implication, besides being faster (among other things), it means that all of your autoincrement fields will start over at 1.  I really like that outcome- nice and tidy.

Use DELETE FROM TABLE to Not Re-Use IDs

What I usually do (at least in CakePHP) is a deleteAll, which executes a DELETE FROM mysql_table_name which clears out all of the rows in a table.  The major difference- MySQL goes row by row, deleting each row, resulting in a performance hit vs. truncating the same data.  The consequence of only deleting data and not re-creating the table, is that your autoincrement value will remain unchanged.