I can't help but wonder how this is in any way relevant to the
original question.
On Fri, Jun 4, 2010 at 6:12 PM, Martin Gainty <mgai...@hotmail.com> wrote:
>
> 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
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
