![]() Now that I have modified my process to detect the empty table and abort before calling DELETE, I can manually watch for the (data-specific) condition that's causing my list-of-rows-to-delete to occasionally become empty.ĮDIT: Even after fixing the empty-in-list problem, my query was greatly sped up by switching from the " IN (SELECT * temp_table) " version to a JOIN version, similar to what is suggested in comments by ypercube above. ![]() Which gave DELETE FROM live_db.test_table WHERE test_idīased on this answer:, it seems like that empty table was probably compared against each of my 106 million rows, making the do-nothing DELETE take 25 minutes. IN (SELECT * FROM temp_table_B) ORDER BY test_id ASC LIMIT N Then, with an empty temp_table_B, I was running step 4: DELETE FROM live_db.test_table WHERE test_id (see question description for step/table context) Test_id int(10) unsigned NOT NULL DEFAULT '0'īecause of how I was choosing test_id values in steps 1 and 2, temp_table B would sometimes be empty. (From Comment) CREATE TEMPORARY TABLE temp_table_B ( Trx #rec lock waits 0 #table lock waits 0 WHERE test_id IN (SELECT * FROM temp_table_B) Output selected from SHOW ENGINE INNODB STATUS (some things redacted): -TRANSACTION 6457899980, ACTIVE 14 sec starting index read, thread declared inside InnoDB 3008Ģ42717 lock struct(s), heap size 25507368, 5532268 row lock(s) Now that the table is half empty, step 4 is hanging, even for N = 10īefore the table was half-empty, it worked fine and N = 1000 took ~ 1 secĪdding an EXPLAIN in the stored procedure lets me know MySQL is planning on using the PRIMARY index for the DELETE, as I hoped it would: id select_type table type possible_keys key key_len ref rows Extraġ PRIMARY test_runs index NULL PRIMARY 4 NULL 10 Using whereĨ DEPENDENT SUBQUERY tests_to_drop ALL NULL NULL NULL NULL 1 Using where
0 Comments
Leave a Reply. |