Hi Brian-
i think the best way to ensure your dates are using YYYY-MM-DD format is for
your dml to reference dates with DATE_FORMAT('YYYY-MM-DD','%Y-%m-%d') e.g.
mysql> select DEIT_EVENT_SEQUENCE_ID,DEIT_EVENT_STATUS_CODE,DEIT_EVENT_DATE
from DEIT;
+------------------------+------------------------+-----------------+
| DEIT_EVENT_SEQUENCE_ID | DEIT_EVENT_STATUS_CODE | DEIT_EVENT_DATE |
+------------------------+------------------------+-----------------+
| 1 | 1 | 2006-09-04
|
| 2 | 2 | 2006-09-05
|
| 3 | 3 | 2006-09-06
|
+------------------------+------------------------+-----------------+
3 rows in set (0.00 sec)
mysql> delete from DEIT where
DEIT_EVENT_DATE<DATE_FORMAT('2006-09-05','%Y-%m-%d');
Query OK, 1 row affected (0.02 sec)
--the record is deleted so lets select to make sure
mysql> select DEIT_EVENT_SEQUENCE_ID,DEIT_EVENT_STATUS_CODE,DEIT_EVENT_DATE
from DEIT;
+------------------------+------------------------+-----------------+
| DEIT_EVENT_SEQUENCE_ID | DEIT_EVENT_STATUS_CODE | DEIT_EVENT_DATE |
+------------------------+------------------------+-----------------+
| 2 | 2 | 2006-09-05 |
| 3 | 3 | 2006-09-06 |
+------------------------+------------------------+-----------------+
2 rows in set (0.00 sec)
hth
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est
interdite. Ce message sert à l'information seulement et n'aura pas n'importe
quel effet légalement obligatoire. Étant donné que les email peuvent facilement
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité
pour le contenu fourni.
> From: br...@briandunning.com
> Subject: Best way to purge old records from a huge table?
> Date: Fri, 4 Jun 2010 08:10:07 -0700
> To: mysql@lists.mysql.com
>
> Hey all -
>
> I have a table with 12,000,000 records spread over about 6 years. I'm trying
> to delete all but the last 2 years, but no matter how small of a group I try
> to delete at a time, it keeps hanging up the server and I eventually have to
> restart MySQL. The table looks like this:
>
> `creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update
> CURRENT_TIMESTAMP,
> `lat` double NOT NULL default '0',
> `lon` double NOT NULL default '0',
> `referer` int(12) NOT NULL default '0',
> PRIMARY KEY (`referer`,`lat`,`lon`),
> KEY `creation` (`creation`,`referer`)
>
> And the query I've been trying looks like this:
>
> delete from tablename where `creation` < '2006-04-01 00:00:00'
>
> ...trying to do the oldest 1 month of records at a time. So am I just trying
> a really inefficient query? Is there a better way to do this?
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
>
_________________________________________________________________
Hotmail has tools for the New Busy. Search, chat and e-mail from your inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_1
