New answers tagged delete
Plan A: Create temp table with num and max(id). 400K rows Chunk that table to do only 1000 deletes at a time; there would be a JOIN in the DELETE to the temp table. More on Chunking for DELETE. Plan B: You need to keep 60% of the table, correct? So, it may be faster to rebuild the table with only the desired rows. CREATE TABLE new LIKE activity; ...
Any attempt to do this delete in a single query is likely to take hours, maybe days. If that is OK, then the code by @ypercube is probably good enough. Otherwise, I would recommend tackling it in stages. First find the items to delete. This will be relatively fast and non-invasive. CREATE TEMPORARY TABLE t ( PRIMARY KEY(item_id) ) SELECT ...
One way to write the DELETE statement: DELETE FROM parsed AS p WHERE p.timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) AND EXISTS ( SELECT * FROM parsed AS n WHERE p.item_id = n.item_id AND p.timestamp < n.timepstamp ) ;
I don't have MySQL on my PC so you may need to adjust a little, but here is how I would solve it using MS SQL Server. -- Create Demo Table DECLARE @MyTable TABLE ( IdField INT, DateField DATE, GroupField CHAR(1) ) INSERT INTO @MyTable ( [IdField], [DateField], [GroupField] ) SELECT 1, '2015-03-01', 'A' UNION SELECT 2, '2015-04-01', 'A' UNION ...
I think he best way is to copy those data you want to keep to another table, then delete them, then copy back in. The main problem is that it is not simple to NOT delete ONE of the rows that are duplicate. By saving those in a separate temporary table - you avoid the issue of not having to delete them.
I had a similar problem. As it turns out, those ON DELETE CASCADE triggers were slowing things down quite a bit, because those cascaded deletions were awfully slow. I solved the problem by creating indexes on the foreign key fields on the referencing tables, and I went from taking a bunch of hours for the deletion to a few seconds.
TRUNCATE does not fire ON DELETE triggers in any 5.x version. But it does have to lock the table and have to wait for any other open transactions that hold locks on the table to release them. That's what the process list is showing. There is a (transaction with a) delete query that is running and has some lock on the table and is blocking the TRUNCATE ...
...once they are no longer in cache, both selecting and deleting the same 12 thousand rows takes ~40 seconds. This seems to indicate that the storage subsystem is inadequate. If this is the cause, SQL Server will probably be waiting with one of the PAGEIOLATCH_XX wait types. It definitely appears that the LOB data must be loaded into memory on the ...
Top 50 recent answers are included