clean-url-rewrites.sql

DROP PROCEDURE IF EXISTS ClearCoreUrlTable;
DELIMITER $$
CREATE PROCEDURE `ClearCoreUrlTable`()
BEGIN
    SET @delete_counter = 0;
    SET @table_name = 'core_url_rewrite';
    SET @total_records = (SELECT count(*) FROM core_url_rewrite WHERE product_id is not null and category_id is not null);

    WHILE @delete_counter < @total_records DO
       SET @varSQL = CONCAT('DELETE FROM ', @table_name,' WHERE category_id IS NOT NULL and product_id IS NOT NULL AND is_system=0 LIMIT 500;');
       PREPARE stmt FROM @varSQL;
       EXECUTE stmt;
       DEALLOCATE PREPARE stmt;
       
       SET @delete_counter = @delete_counter + 500;
    END WHILE;         
END $$
DELIMITER ;