List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:June 4 2010 4:16pm
Subject:Re: Best way to purge old records from a huge table?
View as plain text  
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 <mgainty@stripped> 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: brian@stripped
>> Subject: Best way to purge old records from a huge table?
>> Date: Fri, 4 Jun 2010 08:10:07 -0700
>> To: mysql@stripped
>>
>> 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=1
>>
>
> _________________________________________________________________
> 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
Thread
Best way to purge old records from a huge table?Brian Dunning4 Jun
  • Re: Best way to purge old records from a huge table?Ananda Kumar4 Jun
  • Re: Best way to purge old records from a huge table?Krishna Chandra Prajapati4 Jun
  • RE: Best way to purge old records from a huge table?Martin Gainty4 Jun
    • Re: Best way to purge old records from a huge table?Johan De Meersman4 Jun
  • Re: Best way to purge old records from a huge table?Shawn Green4 Jun