List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:November 4 2011 10:05am
Subject:Re: Deleting Records in Big tables
View as plain text  
PLEASE do not top-post after you got a reply
at the bottom of your quote

sorry, but i can not help you with your application
if it for whatever reason uses the filed 'id' in a where-statement
and your table has no key on this column your table-design is
wrong and you have to add the key

yes this hurts in hughe tables but this is the price not looking at the
table-design at the very first begin of a project

Am 04.11.2011 11:00, schrieb Ananda Kumar:
> 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


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
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