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.rei...@thelounge.net>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
signature.asc
Description: OpenPGP digital signature
