Minify a Magento 2 database

minify-magento2.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;
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
);



-- ----------------------------------
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_order`;
	while @remaining_orders > 50 do
	    delete from `sales_order` where entity_id < (
            select min(entity_id) from (
                select entity_id from `sales_order`
                order by entity_id desc limit 50
            ) as order_ids
        ) limit 1000;

		select @remaining_orders := count(*) from `sales_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 `quote`;
    while @remaining_quotes > 50 do
        delete from `quote` where entity_id < (
            select min(entity_id) from (
                select entity_id from `quote`
                order by entity_id desc limit 50
            ) as quote_ids
        ) limit 1000;

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

delimiter ;

call minify_quotes();

drop procedure minify_quotes;

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

-- ----------------------------------
select "Deleting all but 50 records in sales reports 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
);
delete from `sales_bestsellers_aggregated_daily` where id < (
	select min(id) from (
		select id from `sales_bestsellers_aggregated_daily`
		order by id desc limit 50
	) as bestsellers_aggregated_daily_ids
);

drop procedure if exists minify_bestsellers_daily;

delimiter #

create procedure minify_bestsellers_daily()
begin
	select @remaining_bestsellers_daily := count(*) from `sales_bestsellers_aggregated_daily`;
	while @remaining_bestsellers_daily > 50 do
	    delete from `sales_bestsellers_aggregated_daily` where id < (
            select min(id) from (
                select id from `sales_bestsellers_aggregated_daily`
                order by id desc limit 50
            ) as bestseller_daily_ids
        ) limit 1000;

		select @remaining_bestsellers_daily := count(*) from `sales_bestsellers_aggregated_daily`;
	end while;
end #

delimiter ;

call minify_bestsellers_daily();

drop procedure minify_bestsellers_daily;



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

-- ----------------------------------
select "Deleting all but 50 records in coupon reports tables" debug;
delete from `salesrule_coupon_aggregated` where id < (
	select min(id) from (
		select id from `salesrule_coupon_aggregated`
		order by id desc limit 50
	) as coupon_aggregated_ids
);
delete from `salesrule_coupon_aggregated_order` where id < (
	select min(id) from (
		select id from `salesrule_coupon_aggregated_order`
		order by id desc limit 50
	) as coupon_aggregated_order_ids
);
delete from `salesrule_coupon_aggregated_updated` where id < (
	select min(id) from (
		select id from `salesrule_coupon_aggregated_updated`
		order by id desc limit 50
	) as coupon_aggregated_updated_ids
);
delete from `sales_bestsellers_aggregated_monthly` where id < (
	select min(id) from (
		select id from `sales_bestsellers_aggregated_monthly`
		order by id desc limit 50
	) as coupon_aggregated_updated_ids
);
delete from `sales_bestsellers_aggregated_yearly` where id < (
	select min(id) from (
		select id from `sales_bestsellers_aggregated_yearly`
		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 tax report records" 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 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 visitor logs" debug;
delete from `customer_visitor` where visitor_id < (
	select min(visitor_id) from (
		select visitor_id from `customer_visitor`
		order by visitor_id desc limit 50
	) as visitor_ids
);



-- ----------------------------------
select "Deleting all but 50 search queries" debug;
delete from `search_query` where query_id < (
	select min(query_id) from (
		select query_id from `search_query`
		order by query_id desc limit 50
	) as search_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 "Deleting all but 50 admin logging events" debug;
delete from `magento_logging_event` where log_id < (
    select min(log_id) from (
        select log_id from `magento_logging_event`
        order by log_id desc limit 50
    ) as log_ids
);

-- ----------------------------------
select "Deleting all but 50 magento_operations records" debug;
delete from `magento_operation` where id < (
    select min(id) from (
        select id from `magento_operation`
        order by id desc limit 50
    ) as operation_ids
);

-- ----------------------------------
select "Deleting all but 50 report events" debug;
drop procedure if exists minify_report_events;

delimiter #

create procedure minify_report_events()
begin
	select @remaining_events := count(*) from `report_event`;
	while @remaining_events > 50 do
	    delete from `report_event` where event_id < (
            select min(event_id) from (
                select event_id from `report_event`
                order by event_id desc limit 50
            ) as event_ids
        ) limit 1000;

		select @remaining_events := count(*) from `report_event`;
	end while;
end #

delimiter ;

call minify_report_events();

drop procedure minify_report_events;

-- ----------------------------------
select "Deleting all but 50 records in product reports" debug;
delete from `report_viewed_product_aggregated_daily` where id < (
    select min(id) from (
        select id from `report_viewed_product_aggregated_daily`
        order by id desc limit 50
    ) as product_daily_ids
);
delete from `report_viewed_product_aggregated_monthly` where id < (
    select min(id) from (
        select id from `report_viewed_product_aggregated_monthly`
        order by id desc limit 50
    ) as product_monthly_ids
);
delete from `report_viewed_product_aggregated_yearly` where id < (
    select min(id) from (
        select id from `report_viewed_product_aggregated_yearly`
        order by id desc limit 50
    ) as product_yearly_ids
);
delete from `report_viewed_product_index` where index_id < (
    select min(index_id) from (
        select index_id from `report_viewed_product_index`
        order by index_id desc limit 50
    ) as index_ids
);

-- ----------------------------------
select "Deleting all but 50 reporting_users records" debug;
delete from `reporting_users` where entity_id < (
    select min(entity_id) from (
        select entity_id from `reporting_users`
        order by entity_id desc limit 50
    ) as reporting_user_ids
);



-- ----------------------------------
select "Truncating tables" debug;
truncate table `cron_schedule`;



-- ----------------------------------
select "Deleting related rows in tables without foreign keys" debug;
delete cg.*
from `customer_grid_flat` cg
left join `customer_entity` c on (cg.entity_id = c.entity_id)
where c.entity_id is null;

delete cg.*
from `sales_creditmemo_grid` cg
left join `sales_creditmemo` c on (cg.entity_id = c.entity_id)
where c.entity_id is null;

delete ig.*
from `sales_invoice_grid` ig
left join `sales_invoice` i on (ig.entity_id = i.entity_id)
where i.entity_id is null;

delete og.*
from `sales_order_grid` og
left join sales_order o on (og.entity_id = o.entity_id)
where o.entity_id is null;

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

delete sg.*
from `sales_shipment_grid` sg
left join `sales_shipment` s on (sg.entity_id = s.entity_id)
where s.entity_id is null;

delete cl.*
from `customer_log` cl
left join `customer_entity` c on (cl.customer_id = c.entity_id)
where c.entity_id is null;

delete pas.*
from `product_alert_stock` pas
left join `customer_entity` c on (pas.customer_id = c.entity_id)
where c.entity_id is null;



-- ------------------------------------
select "Deleting unneeded records in sequence tables" debug;
delete ss.*
from `sequence_salesrule` ss
left join `salesrule` s on (ss.sequence_value = s.rule_id)
where s.rule_id is null;