On Mar 18, 12:43 pm, lister <listerofsme...@hotmail.com> wrote:
> Hi all,
>
> I have a large busy myisam table which has a lot of inserts and
> deletes acting on it (as well as selects and updates).
>
> I am sure I have read somewhere that it can be more efficient not to
> delete a row at all, but to just set a "deleted" flag on it, and then
> perhaps do a bulk delete at some quiet time. I can't find where I read
> it now though.
>
> Is this the case? Are there only particular use cases where it makes
> sense?
>
> Thanks

I tend to doubt if this saves you any processing time at all.

After all, an I/O is an I/O, and the system is going to pull down a
block, modify it, and put it back, no matter whether that's an update
or a delete.

And, although I'm not expert on database internals, I'd be willing to
bet that internally the system just flags the row for deletion
anyway. Then later on, as time and algorithms permit, it puts the row
back into the pool of available space. So all you'd accomplish with
your flag is duplicating the internal actions, and not saving hardly
anything at all.

Other than that --

Peter H. Coffin is 100% correct in that this method allows you to
"undelete" a row, something that can be very important, depending on
the application.

And Michael Austin is 100% correct that possibly your biggest time
constraint will be man-hours changing all the application queries to
add ("and rec_deleted is null") to each and every SQL that includes
this table.

Bottom line: unless it's needed by the application, don't bother.

Hope that helps.