Tag Info

New answers tagged

0

The EXPLAIN took so long because it evaluated the subquery. Perhaps the subquery took so long because of lack of INDEX(field, id). When deleting a large chunk of a table, it is often faster to copy everything that you want to keep into a new table, then use RENAME to swap tables. Or, you could do the deletes in chunks of 100-1000, walking through the ...


0

I see what the query is doing. You are trying to DELETE a ton of rows and keep the last inserted id for every field. I have a much better method. DROP TABLE IF EXISTS keys_to_keep; CREATE TABLE keys_to_keep ( id INT NOT NULL, PRIMARY KEY (id) ); INSERT INTO keys_to_keep SELECT MAX(id) FROM mytable GROUP BY field; CREATE TABLE mytable_new LIKE ...



Top 50 recent answers are included