Return to Answer

Make a temp table, switch it in and out, and copy the last 30 days data into it.

#
# Make empty temp table
#
CREATE TABLE NOTIFICATION_NEW LIKE NOTIFICATION;
#
# Switch in new empty temp table
#
RENAME TABLE NOTIFICATION TO NOTIFICATION_OLD,NOTIFICATION_NEW TO NOTIFICATION;
#
# Retrieve last 30 days data 
#
INSERT INTO NOTIFICATION SELECT * FROM NOTIFICATION_OLD
WHERE CreatedAt < DATE_SUB(CURDATE(), INTERVAL 30 day);

In your off hours, drop the old table

DROP TABLE NOTIFICATION_OLD;

Here are the Advantages to doing DELETEs like this

1. NOTIFICATION is emptied fast by means switching in an empty table.
2. NOTIFICATION is immediately available for new INSERTs
3. The remaining 30 days are added back into NOTIFICATION while new INSERTs can take place.
4. Dropping the old version of NOTIFICATION does not interfere with new INSERTs
5. NOTE : I have recommended doing bait-and-switch for table DELETEs before : (See my July 19, 2012 post : Optimizing DELETE Query on MySQL MEMORY Table)

Give it a Try !!!

Make a temp table, switch it in and out, and copy the last 30 days data into it.

#
# Make empty temp table
#
CREATE TABLE NOTIFICATION_NEW LIKE NOTIFICATION;
#
# Switch in new empty temp table
#
RENAME TABLE NOTIFICATION TO NOTIFICATION_OLD,NOTIFICATION_NEW TO NOTIFICATION;
#
# Retrieve last 30 days data 
#
INSERT INTO NOTIFICATION SELECT * FROM NOTIFICATION_OLD
WHERE CreatedAt >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

In your off hours, drop the old table

DROP TABLE NOTIFICATION_OLD;

Here are the Advantages to doing DELETEs like this

1. NOTIFICATION is emptied fast by means switching in an empty table.
2. NOTIFICATION is immediately available for new INSERTs
3. The remaining 30 days are added back into NOTIFICATION while new INSERTs can take place.
4. Dropping the old version of NOTIFICATION does not interfere with new INSERTs
5. NOTE : I have recommended doing bait-and-switch for table DELETEs before : (See my July 19, 2012 post : Optimizing DELETE Query on MySQL MEMORY Table)

Give it a Try !!!

Make a temp table, switch it in and out, and copy the last 30 days data into it.

#
# Make empty temp table
#
CREATE TABLE NOTIFICATION_NEW LIKE NOTIFICATION;
#
# Switch in new empty temp table
#
RENAME TABLE NOTIFICATION TO NOTIFICATION_OLD,NOTIFICATION_NEW TO NOTIFICATION;
#
# Retrieve last 30 days data 
#
INSERT INTO NOTIFICATION SELECT * FROM NOTIFICATION_OLD
WHERE CreatedAt < DATE_SUB(CURDATE(), INTERVAL 30 day);

In your off hours, drop the old table

DROP TABLE NOTIFICATION_OLD;

Here are the Advantages to doing DELETEs like this

1. NOTIFICATION is emptied fast by means switching in an empty table.
2. NOTIFICATION is immediately available for new INSERTs
3. The remaining 30 days are added back into NOTIFICATION while new INSERTs can take place.
4. Dropping the old version of NOTIFICATION does not interfere with new INSERTs
5. NOTE : I have recommended doing bait-and-switch for table DELETEs before : (See my July 19, 2012 post : Optimizing DELETE Query on MySQL MEMORY Table)

Give it a Try !!!

Make a temp table, switch it in and out, and copy the last 30 days data into it.

#
# Make empty temp table
#
CREATE TABLE NOTIFICATION_NEW LIKE NOTIFICATION;
#
# Switch in new empty temp table
#
RENAME TABLE NOTIFICATION TO NOTIFICATION_OLD,NOTIFICATION_NEW TO NOTIFICATION;
#
# Retrieve last 30 days data 
#
INSERT INTO NOTIFICATION SELECT * FROM NOTIFICATION_OLD
WHERE CreatedAt < DATE_SUB(CURDATE(), INTERVAL 30 day);

In your off hours, drop the old table

DROP TABLE NOTIFICATION_OLD;

Here are the Advantages to doing DELETEs like this

1. NOTIFICATION is emptied fast by means switching in an empty table.
2. NOTIFICATION is immediately available for new INSERTs
3. The remaining 30 days are added back into NOTIFICATION while new INSERTs can take place.
4. Dropping the old version of NOTIFICATION does not interfere with new INSERTs
5. NOTE : I have recommended doing bait-and-switch for table DELETEs before : (See my July 19, 2012 post : Optimizing DELETE Query on MySQL MEMORY Table)

Give it a Try !!!

Make a temp table, switch it in and out, and copy the last 30 days data into it.

#
# Make empty temp table
#
CREATE TABLE NOTIFICATION_NEW LIKE NOTIFICATION;
#
# Switch in new empty temp table
#
RENAME NOTIFICATION TO NOTIFICATION_OLD,NOTIFICATION_NEW TO NOTIFICATION;
#
# Retrieve last 30 days data 
#
INSERT INTO NOTIFICATION SELECT * FROM NOTIFICATION_OLD
WHERE CreatedAt < DATE_SUB(CURDATE(), INTERVAL 30 day);

In your off hours, drop the old table

DROP TABLE NOTIFICATION_OLD;

Here are the Advantages to doing DELETEs like this

1. NOTIFICATION is emptied fast by means switching in an empty table.
2. NOTIFICATION is immediately available for new INSERTs
3. The remaining 30 days are added back into NOTIFICATION while new INSERTs can take place.
4. Dropping the old version of NOTIFICATION does not interfere with new INSERTs
5. NOTE : I have recommended doing bait-and-switch for table DELETEs before : (See my July 19, 2012 post : Optimizing DELETE Query on MySQL MEMORY Table)

Give it a Try !!!

Make a temp table, switch it in and out, and copy the last 30 days data into it.

#
# Make empty temp table
#
CREATE TABLE NOTIFICATION_NEW LIKE NOTIFICATION;
#
# Switch in new empty temp table
#
RENAME TABLE NOTIFICATION TO NOTIFICATION_OLD,NOTIFICATION_NEW TO NOTIFICATION;
#
# Retrieve last 30 days data 
#
INSERT INTO NOTIFICATION SELECT * FROM NOTIFICATION_OLD
WHERE CreatedAt < DATE_SUB(CURDATE(), INTERVAL 30 day);

In your off hours, drop the old table

DROP TABLE NOTIFICATION_OLD;

Here are the Advantages to doing DELETEs like this

1. NOTIFICATION is emptied fast by means switching in an empty table.
2. NOTIFICATION is immediately available for new INSERTs
3. The remaining 30 days are added back into NOTIFICATION while new INSERTs can take place.
4. Dropping the old version of NOTIFICATION does not interfere with new INSERTs
5. NOTE : I have recommended doing bait-and-switch for table DELETEs before : (See my July 19, 2012 post : Optimizing DELETE Query on MySQL MEMORY Table)

Give it a Try !!!