Minify a Magento 1 database

minify-magento1.sql

-- ----------------------------------
select "Deleting all but 50 customers" debug;
drop procedure if exists minify_customers;

delimiter #

create procedure minify_customers()
begin
    select @remaining_customers := count(*) from `customer_entity`;
    while @remaining_customers > 50 do
        delete from `customer_entity` where entity_id < (
            select min(entity_id) from (
                select entity_id from `customer_entity`
                order by entity_id desc limit 50
            ) as customer_ids
        ) limit 1000;

        select @remaining_customers := count(*) from `customer_entity`;
    end while;
end #

delimiter ;

call minify_customers();

drop procedure minify_customers;

-- ----------------------------------
select "Deleting all but 50 subscribers" debug;
drop procedure if exists minify_subscribers;

delimiter #

create procedure minify_subscribers()
begin
    select @remaining_subscribers := count(*) from `newsletter_subscriber`;
    while @remaining_subscribers > 50 do
        delete from `newsletter_subscriber` where subscriber_id < (
            select min(subscriber_id) from (
                select subscriber_id from `newsletter_subscriber`
                order by subscriber_id desc limit 50
            ) as subscriber_ids
        ) limit 1000;

        select @remaining_subscribers := count(*) from `newsletter_subscriber`;
    end while;
end #

delimiter ;

call minify_subscribers();

drop procedure minify_subscribers;



-- ----------------------------------
select "Deleting all but 50 orders" debug;

drop procedure if exists minify_orders;

delimiter #

create procedure minify_orders()
begin
    select @remaining_orders := count(*) from `sales_flat_order`;
	while @remaining_orders > 50 do
        delete from `sales_flat_order` where entity_id < (
            select min(entity_id) from (
                select entity_id from `sales_flat_order`
                order by entity_id desc limit 50
            ) as order_ids
        ) limit 1000;

		select @remaining_orders := count(*) from `sales_flat_order`;
	end while;
end #

delimiter ;

call minify_orders();

drop procedure minify_orders;

-- ----------------------------------
select "Deleting all but 50 quotes" debug;

drop procedure if exists minify_quotes;

delimiter #

create procedure minify_quotes()
begin
    select @remaining_quotes := count(*) from `sales_flat_quote`;
    while @remaining_quotes > 50 do
        delete from `sales_flat_quote` where entity_id < (
            select min(entity_id) from (
                select entity_id from `sales_flat_quote`
                order by entity_id desc limit 50
            ) as quote_ids
        ) limit 1000;

        select @remaining_quotes := count(*) from `sales_flat_quote`;
    end while;
end #

delimiter ;

call minify_quotes();

drop procedure minify_quotes;

-- ----------------------------------
select "Deleting all but 50 rmas" debug;
delete from `rma` where rma_id < (
	select min(rma_id) from (
		select rma_id from `rma`
		order by rma_id desc limit 50
	) as rma_ids
);

-- ----------------------------------
select "Deleting all but 50 in sales report tables" debug;
delete from `sales_invoiced_aggregated` where id < (
	select min(id) from (
		select id from `sales_invoiced_aggregated`
		order by id desc limit 50
	) as invoiced_aggregated_ids
);
delete from `sales_invoiced_aggregated_order` where id < (
	select min(id) from (
		select id from `sales_invoiced_aggregated_order`
		order by id desc limit 50
	) as invoiced_aggregated_order_ids
);
delete from `sales_order_aggregated_created` where id < (
	select min(id) from (
		select id from `sales_order_aggregated_created`
		order by id desc limit 50
	) as order_aggregated_created_ids
);
delete from `sales_order_aggregated_updated` where id < (
	select min(id) from (
		select id from `sales_order_aggregated_updated`
		order by id desc limit 50
	) as order_aggregated_updated_ids
);
delete from `sales_refunded_aggregated` where id < (
	select min(id) from (
		select id from `sales_refunded_aggregated`
		order by id desc limit 50
	) as refunded_aggregated_ids
);
delete from `sales_refunded_aggregated_order` where id < (
	select min(id) from (
		select id from `sales_refunded_aggregated_order`
		order by id desc limit 50
	) as refunded_aggregated_order_ids
);
delete from `sales_shipping_aggregated` where id < (
	select min(id) from (
		select id from `sales_shipping_aggregated`
		order by id desc limit 50
	) as shipping_aggregated_ids
);
delete from `sales_shipping_aggregated_order` where id < (
	select min(id) from (
		select id from `sales_shipping_aggregated_order`
		order by id desc limit 50
	) as shipping_aggregated_order_ids
);



-- ----------------------------------
select "Deleting all but 50 sales rules" debug;
delete from `salesrule` where rule_id < (
	select min(rule_id) from (
		select rule_id from `salesrule`
		order by rule_id desc limit 50
	) as salesrule_ids
);

-- ----------------------------------
select "Deleting all but 50 in coupon report tables" debug;
delete from `coupon_aggregated` where id < (
	select min(id) from (
		select id from `coupon_aggregated`
		order by id desc limit 50
	) as coupon_aggregated_ids
);
delete from `coupon_aggregated_order` where id < (
	select min(id) from (
		select id from `coupon_aggregated_order`
		order by id desc limit 50
	) as coupon_aggregated_order_ids
);
delete from `coupon_aggregated_updated` where id < (
	select min(id) from (
		select id from `coupon_aggregated_updated`
		order by id desc limit 50
	) as coupon_aggregated_updated_ids
);



-- ----------------------------------
select "Deleting all but 50 tax rates" debug;
delete from `tax_calculation_rate` where tax_calculation_rate_id < (
	select min(tax_calculation_rate_id) from (
		select tax_calculation_rate_id from `tax_calculation_rate`
		order by tax_calculation_rate_id desc limit 50
	) as tax_rate_ids
);

-- ----------------------------------
select "Deleting all but 50 in tax report tables" debug;
delete from `tax_order_aggregated_created` where id < (
	select min(id) from (
		select id from `tax_order_aggregated_created`
		order by id desc limit 50
	) as tax_aggregated_created_ids
);
delete from `tax_order_aggregated_updated` where id < (
	select min(id) from (
		select id from `tax_order_aggregated_updated`
		order by id desc limit 50
	) as tax_aggregated_updated_ids
);



-- ----------------------------------
select "Deleting all but 50 admin logs" debug;
delete from `adminlog_activities` where id < (
	select min(id) from (
		select id from `adminlog_activities`
		order by id desc limit 50
	) as adminlog_activities_ids
);

-- ----------------------------------
select "Deleting all but 50 admin notifications" debug;
delete from `adminnotification_inbox` where notification_id < (
	select min(notification_id) from (
		select notification_id from `adminnotification_inbox`
		order by notification_id desc limit 50
	) as adminnotification_inbox_ids
);

-- ----------------------------------
select "Deleting all but 50 customer logs" debug;
delete from `log_customer` where log_id < (
	select min(log_id) from (
		select log_id from `log_customer`
		order by log_id desc limit 50
	) as log_customer_ids
);

-- ----------------------------------
select "Deleting all but 50 URL logs" debug;
delete from `log_url` where url_id < (
	select min(url_id) from (
		select url_id from `log_url`
		order by url_id desc limit 50
	) as log_url_ids
);
delete from `log_url_info` where url_id < (
	select min(url_id) from (
		select url_id from `log_url_info`
		order by url_id desc limit 50
	) as log_url_info_ids
);



-- ----------------------------------
select "Deleting all but 50 search queries" debug;
delete from `catalogsearch_query` where query_id < (
	select min(query_id) from (
		select query_id from `catalogsearch_query`
		order by query_id desc limit 50
	) as catalogsearch_query_ids
);



-- ----------------------------------
select "Deleting all but 50 review votes" debug;
delete from `rating_option_vote` where vote_id < (
	select min(vote_id) from (
		select vote_id from `rating_option_vote`
		order by vote_id desc limit 50
	) as rating_option_vote_ids
);



-- ----------------------------------
select "Truncating tables" debug;
SET FOREIGN_KEY_CHECKS=0;
truncate table `core_cache_tag`;
truncate table `cron_schedule`;
truncate table `dataflow_batch_export`;
truncate table `dataflow_profile_history`;
truncate table `poll_vote`;
truncate table `index_process_event`;
truncate table `index_event`;
SET FOREIGN_KEY_CHECKS=1;



-- ----------------------------------
select "Deleting related rows from tables without foreign keys" debug;
delete rc.*
from `rma_comments` rc
left join `rma` r on (rc.rma_id = r.rma_id)
where r.rma_id is null;

delete rh.*
from `rma_history` rh
left join `rma` r on (rh.rh_rma_id = r.rma_id)
where r.rma_id is null;

delete rp.*
from `rma_products` rp
left join `rma` r on (rp.rp_rma_id = r.rma_id)
where r.rma_id is null;

delete ot.*
from `sales_order_tax` ot
left join `sales_flat_order` o on (ot.order_id = o.entity_id)
where o.entity_id is null;