Menu
I have a query like this which takes a really long time to run. The table is around 4 million rows.
DELETE FROM TABLE WHERE value_was IS NULL OR value_was <= value_now;
I'm hoping I could create an index for (value_was, value_now) so I could do something like
DELETE FROM TABLE WHERE
ID1 IN (SELECT ID1 from TABLE where value_was IS NULL)
OR ID2 IN (SELECT ID2 FROM TABLE WHERE value_was <= value_now);
This table doesn't have primary key. It has two composite keys. And I guess I cannot use the same table in subquery, but how do I improve the performance of the first query?
Thanks very much any suggestion would be much appreciated.
Updated: The db is innoDB
412
To reclaim unused index space under these circumstances, use OPTIMIZE TABLE . If you are going to delete many rows from a table, it might be faster to use DELETE QUICK followed by OPTIMIZE TABLE . This rebuilds the index rather than performing many index block merge operations.
When performing a DELETE against a large number of rows in the Prices table, the DELETE becomes progressively slower. If deleting 15,000 rows it runs in about 15 seconds. 20K rows takes 3 or 4 minutes. 40,000 rows takes 15 minutes, 100,000 rows runs for well over an hour.
This will theoretically get around the locked table issue because other queries will be able to make it into the queue and run in between the deletes. But it will still spike the load on the database quite a bit and will take a long time to run. Rename the table and recreate the existing table (it'll now be empty).
Remove any unnecessary indexes on the table, paying particular attention to UNIQUE indexes as these disable change buffering. Don't use a UNIQUE index unless you need it; instead, employ a regular INDEX. Take a look at your slow query log every week or two. Pick the slowest three queries and optimize those.
Due to the way data is stored, as an internal linked list, innoDB tables are inherently slow at huge DELETE operations. Changing the storage type to myISAM should make the operation an awful lot faster - I've seen 100x improvements in similar situations.
88
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
