From: Johan De Meersman Date: June 4 2010 4:16pm Subject: Re: Best way to purge old records from a huge table? List-Archive: http://lists.mysql.com/mysql/221824 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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 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_DA= TE from DEIT; > +------------------------+------------------------+-----------------+ > | DEIT_EVENT_SEQUENCE_ID | DEIT_EVENT_STATUS_CODE | DEIT_EVENT_DATE | > +------------------------+------------------------+-----------------+ > | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= 1 | 1 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 2006-09-04 =A0 =A0 =A0| > | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= 2 | 2 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 2006-09-05 =A0 =A0 =A0| > | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= 3 | 3 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 2006-09-06 =A0 =A0 =A0| > +------------------------+------------------------+-----------------+ > 3 rows in set (0.00 sec) > > > > mysql> delete from DEIT where DEIT_EVENT_DATE 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_DA= TE from DEIT; > +------------------------+------------------------+-----------------+ > | DEIT_EVENT_SEQUENCE_ID | DEIT_EVENT_STATUS_CODE | DEIT_EVENT_DATE | > +------------------------+------------------------+-----------------+ > | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 | 2 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0| 2006-09-05 =A0 =A0 =A0| > | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A03 | 3 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0| 2006-09-06 =A0 =A0 =A0| > +------------------------+------------------------+-----------------+ > 2 rows in set (0.00 sec) > > > hth > > Martin Gainty > ______________________________________________ > Verzicht und Vertraulichkeitanmerkung/Note de d=E9ni et de confidentialit= =E9 > > Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfae= nger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiter= leitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient l= ediglich dem Austausch von Informationen und entfaltet keine rechtliche Bin= dungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen w= ir keine Haftung fuer den Inhalt uebernehmen. > > Ce message est confidentiel et peut =EAtre privil=E9gi=E9. Si vous n'=EAt= es pas le destinataire pr=E9vu, nous te demandons avec bont=E9 que pour sat= isfaire informez l'exp=E9diteur. N'importe quelle diffusion non autoris=E9e= ou la copie de ceci est interdite. Ce message sert =E0 l'information seule= ment et n'aura pas n'importe quel effet l=E9galement obligatoire. =C9tant d= onn=E9 que les email peuvent facilement =EAtre sujets =E0 la manipulation, = nous ne pouvons accepter aucune responsabilit=E9 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 tr= ying 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 CURREN= T_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 tr= ying 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=3Dmgainty@stripped >> > > _________________________________________________________________ > Hotmail has tools for the New Busy. Search, chat and e-mail from your inb= ox. > http://www.windowslive.com/campaign/thenewbusy?ocid=3DPID28326::T:WLMTAGL= :ON:WL:en-US:WM_HMP:042010_1 --=20 Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel