List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:November 4 2011 10:00am
Subject:Re: Deleting Records in Big tables
View as plain text  
Create PROCEDURE qrtz_purge() BEGIN
   declare l_id bigint(20);
   declare NO_DATA INT DEFAULT 0;
   DECLARE LST_CUR CURSOR FOR select id from table_name where id> 123;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_DATA = -1;
   OPEN LST_CUR;
   SET NO_DATA = 0;
   FETCH LST_CUR INTO l_id;
   WHILE NO_DATA = 0 DO
      Delete from table_name where id=l_id
      COMMIT;
      SET NO_DATA = 0;
      FETCH LST_CUR INTO l_id;
   END WHILE;
   CLOSE LST_CUR;
   END

On Fri, Nov 4, 2011 at 2:40 PM, Reindl Harald <h.reindl@stripped>wrote:

>
>
> Am 04.11.2011 08:22, schrieb Adarsh Sharma:
> > delete from metadata where id>2474;
> > but it takes hours to complete.
> >
> > CREATE TABLE `metadata` (
> >  `meta_id` bigint(20) NOT NULL AUTO_INCREMENT,
> >  `id` bigint(20) DEFAULT NULL,
> >  `url` varchar(800) DEFAULT NULL,
> >  `meta_field` varchar(200) DEFAULT NULL,
> >  `meta_value` varchar(2000) DEFAULT NULL,
> >  `dt_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
> >  PRIMARY KEY (`meta_id`)
> > ) ENGINE=InnoDB AUTO_INCREMENT=388780373 ;
> >
> > Please let me know any quickest way to do this.
> > I tried to create indexes in these tables on id, but this too takes time
>
> well i guess you have to sit out add the key
> wrong table design having an id-column without a key or
> something weird in the application not using the primary
> key for such operations
>
>
>

Thread
Deleting Records in Big tablesAdarsh Sharma4 Nov
  • Re: Deleting Records in Big tablesAnanda Kumar4 Nov
    • Re: Deleting Records in Big tablesAdarsh Sharma4 Nov
      • Re: Deleting Records in Big tablesAndy Wallace4 Nov
        • Re: Deleting Records in Big tablesDerek Downey4 Nov
          • Re: Deleting Records in Big tablesAndy Wallace4 Nov
  • Re: Deleting Records in Big tablesReindl Harald4 Nov
    • Re: Deleting Records in Big tablesAnanda Kumar4 Nov
      • Re: Deleting Records in Big tablesReindl Harald4 Nov
    • Re: Deleting Records in Big tablesJohan De Meersman4 Nov
  • Re: Deleting Records in Big tablesmos9 Nov
    • Re: Deleting Records in Big tablesJohan De Meersman10 Nov