CODEWITHSUNDEEP
mysql
newdeveloper
newdeveloper

Reputation: 702

python mysql loop progressively slow down on delete statement

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

Upvotes: 0

Views: 99

Answers (1)

Rick James
Rick James

Reputation: 142298

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.)

Upvotes: 1

Related Questions