List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:June 4 2010 4:12pm
Subject:RE: Best way to purge old records from a huge table?
View as plain text  
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
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