Taken from http://stackoverflow.com/a/409305/1089331

Timestamps in MySQL generally used to track changes to records, and are updated every time the record is changed. If you want to store a specific value you should use a datetime field.
If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.
- blivet, stackoverflow.com

An important difference is that DATETIME represents a date (as found in a calendar) and a time (as can be observed on a wall clock), while TIMESTAMP represents a well defined point in time. This could be very important if your application handles time zones. How long ago was '2010-09-01 16:31:00'? It depends on what timezone you're in. For me it was just a few seconds ago, for you it may represent a time in the future. If I say 1283351460 seconds since '1970-01-01 00:00:00 UTC', you know exactly what point in time I talk about. (See Nir's excellent answer below). [Downside: valid range].
- MattBianco, stackoverflow.com

In MYSQL 5 and above, TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, and not for other types such as DATETIME.)
By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis, as described here: MySQL Server Time Zone Support
- Nir, stackoverflow.com

I always use DATETIME fields for anything other than row metadata (date created or modified).
As mentioned in the MySQL documentation:

The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
...
The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in.

You're quite likely to hit the lower limit on TIMESTAMPs in general use -- e.g. storing birthdate.
- scronide, stackoverflow.com

The solution focuses on utilizing a view in order to simplify the execution plan produced for a batched delete operation. This is achieved by referencing the given table once, rather than twice which in turn reduces the amount of I/O required.

Before:

delete from t1 where a in (select top (10000) a from t1 order by a);

After:

create view v1 as (select top (10000) * from t1 order by a)

delete from v1

You can also use this method for update operations aswell:

update t1

  set b = 'y'

  from (select top (100) b

        from t1

        order by a ) t1

The posts that provided this information referenced a link (http://blogs.msdn.com/sqlcat/archive/2009/05/21/fa...) provided by Kevin Stephenson of MySpace and Lubor Kollar, a member of the SQL Server Customer Advisory Team. This link is now unavailable but the Internet Archive has a copy of this page at http://web.archive.org/web/20100212155407/http://blogs.msdn.com/sqlcat/archive/2009/05/21/fast-ordered-delete.aspx.

When dealing with deleting data from tables which have foreign key relationships - which is basically the case with any properly designed database - we can disable all the constraints, delete all the data and then re-enable constraints.

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"
-- enable all constraints
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

More on disabling constraints and triggers here.

if some of the tables have identity columns we may want to reseed them.

EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

Note that the behaviour of RESEED differs between brand new table, and one which had some data inserted previously from BOL:

DBCC CHECKIDENT ('table_name', RESEED, newReseedValue)

The current identity value is set to the newReseedValue. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use newReseedValue as the identity. Otherwise, the next row inserted will use newReseedValue + 1. If the value of newReseedValue is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table.