Created
January 8, 2019 14:39
-
-
Save CNanninga/0c45c594ce41d4d21478bb9155dad004 to your computer and use it in GitHub Desktop.
Minify a Magento 2 database
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- ---------------------------------- | |
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment