My db had over 1b lines to remove, so the delete from statement took ages. I turned it around and created a temporary table containing only the wanted data, deleted the original table and renamed the temp table:
– Create a new table without the unwanted rows
CREATE TABLE statistics_bandwidth_new AS
SELECT * FROM statistics_bandwidth
WHERE account_id IS NOT NULL;
– Drop the old bloated table
DROP TABLE statistics_bandwidth;
– Rename the new compacted table
ALTER TABLE statistics_bandwidth_new RENAME TO statistics_bandwidth;
– Recreate the indexes (Thanks to @rockstream)
CREATE INDEX ‘index_statistics_bandwidth_on_at’ ON ‘statistics_bandwidth’ (‘at’);
CREATE INDEX ‘index_statistics_bandwidth_on_account_id_and_timespan_and_at’ ON ‘statistics_bandwidth’ (‘account_id’, ‘timespan’, ‘at’);
– Vacuum the db to reclaim space
vacuum;
This finished in less than 20 minutes on my setup, wheras the delete from statement just hang for 12h+ before I cancelled it
