From: Martin Gainty
Date: June 4 2010 4:12pm
Subject: RE: Best way to purge old records from a huge table?
List-Archive: http://lists.mysql.com/mysql/221823
Message-Id: <BLU142-W335A1D1BE6AB279BA96B5DAED20@phx.gbl>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="_824efe08-820c-4a41-b87a-9722b495fd67_"

--_824efe08-820c-4a41-b87a-9722b495fd67_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Hi Brian-

=20

i think the best way to ensure your dates are using YYYY-MM-DD format is fo=
r your dml to reference dates with DATE_FORMAT('YYYY-MM-DD'=2C'%Y-%m-%d') e=
.g.

=20

mysql> select DEIT_EVENT_SEQUENCE_ID=2CDEIT_EVENT_STATUS_CODE=2CDEIT_EVENT_=
DATE from DEIT=3B
+------------------------+------------------------+-----------------+
| DEIT_EVENT_SEQUENCE_ID | DEIT_EVENT_STATUS_CODE | DEIT_EVENT_DATE |
+------------------------+------------------------+-----------------+
|                                    1 | 1                      | 2006-09-0=
4      |
|                                    2 | 2                      | 2006-09-0=
5      |
|                                    3 | 3                      | 2006-09-0=
6      |
+------------------------+------------------------+-----------------+
3 rows in set (0.00 sec)

=20

mysql> delete from DEIT where DEIT_EVENT_DATE<DATE_FORMAT('2006-09-05'=2C'%=
Y-%m-%d')=3B
Query OK=2C 1 row affected (0.02 sec)

=20

--the record is deleted so lets select to make sure

mysql> select DEIT_EVENT_SEQUENCE_ID=2CDEIT_EVENT_STATUS_CODE=2CDEIT_EVENT_=
DATE from DEIT=3B
+------------------------+------------------------+-----------------+
| 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=20
______________________________________________=20
Verzicht und Vertraulichkeitanmerkung/Note de d=E9ni et de confidentialit=
=E9

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaeng=
er sein=2C 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'=EAtes=
 pas le destinataire pr=E9vu=2C 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=
=2C nous ne pouvons accepter aucune responsabilit=E9 pour le contenu fourni=
.



=20

> From: brian@stripped
> Subject: Best way to purge old records from a huge table?
> Date: Fri=2C 4 Jun 2010 08:10:07 -0700
> To: mysql@stripped
>=20
> Hey all -
>=20
> I have a table with 12=2C000=2C000 records spread over about 6 years. I'm=
 trying to delete all but the last 2 years=2C but no matter how small of a =
group I try to delete at a time=2C it keeps hanging up the server and I eve=
ntually have to restart MySQL. The table looks like this:
>=20
> `creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT=
_TIMESTAMP=2C
> `lat` double NOT NULL default '0'=2C
> `lon` double NOT NULL default '0'=2C
> `referer` int(12) NOT NULL default '0'=2C
> PRIMARY KEY (`referer`=2C`lat`=2C`lon`)=2C
> KEY `creation` (`creation`=2C`referer`)
>=20
> And the query I've been trying looks like this:
>=20
> delete from tablename where `creation` < '2006-04-01 00:00:00'
>=20
> ...trying to do the oldest 1 month of records at a time. So am I just try=
ing a really inefficient query? Is there a better way to do this?
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@stripped
>=20
 		 	   		 =20
_________________________________________________________________
Hotmail has tools for the New Busy. Search=2C chat and e-mail from your inb=
ox.
http://www.windowslive.com/campaign/thenewbusy?ocid=3DPID28326::T:WLMTAGL:O=
N:WL:en-US:WM_HMP:042010_1=

--_824efe08-820c-4a41-b87a-9722b495fd67_--