python mysql loop progressively slow down on delete statement

Question

I have a simple delete statement. It works fast about the first 100 deletes. Then it slows down, then it will sped up. Explain looks pretty fast but the deletes are slow.

for loop
    delete from data.stafftable where staffname='something' and time_deleted<now() and staff_type='teach';

Explain

id: 1
select_type: DELETE
table: stafftable
partitions: NULL
type: range
possible_keys: PRIMARY,staff_name_index,time_deleted_index
key: PRIMARY
key_len: 7
ref: const
rows: 1
filtered: 100.00

Its probably due to your delete forcing the server to do heavy lifting with a string search and date comparison versus looking up by a primary key integer. Also, you didn't end the loop? begin_label: LOOP statement_list END LOOP end_label tutorialspoint.com/mysql/mysql_loop_statement.htm — easleyfixed
– easleyfixed, Commented Jul 26, 2022 at 19:05
Are you doing it with a different staffname each time? Why not do them all at once with where staffname in ('something', 'something else', ...)? — Barmar
– Barmar, Commented Jul 26, 2022 at 19:17
yes staff name is different each loop. this was just an example. also i shorten the python loop in the for statement just for brevity. — newdeveloper
– newdeveloper, Commented Jul 26, 2022 at 19:17

Rick James · Accepted Answer · 2022-07-27 02:03:59Z

1

The more you do it, the more it has to step over rows that it does not need to delete. Whether the table has INDEX(time_deleted) or no useful index, it will get slower and slower.

This would speed it up significantly:

INDEX(staff_type, staffname, time_deleted)

With this index, each time you run the DELETE, the first row it encounters will satisfy the deletion criteria. No need to skip over rows with different staff names or types but matching timestamps.

You may as well add LIMIT 100 to the DELETE statement. Then it will clean up the table much faster. (No, do not fall into the trap of "the bigger the better".)

Oh, now I see the hidden information -- that staffname changes as you delete. The LIMIT 100 may or may not help; but it should not hurt.

(No, "string compares" is not the reason for the slowdown.)

answered Jul 27, 2022 at 2:03

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

newdeveloper Over a year ago

Thank you i was leaning towards a combo index, but I think time_deleted should do the tick as well. But thank you and I will add this combo index and see if that works.

Collectives™ on Stack Overflow

python mysql loop progressively slow down on delete statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related