ThanksButNo wrote:
> 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.
>

There is a slight additional overhead - updating the indexes and
resorting them. However, this must be done at some time, anyway. If
you delete multiple rows in one statement, then the indexes will only be
updated once.

But that's offset by the additional entries in the indexes every time
you SELECT from the table (or even worse need to do a table scan). And
then there's the extra processing rec_deleted is null on every request.

If you're not doing many selects - mostly inserts and deletes, it
probably would save some time to mark the row(s) as deleted and later
delete all at once. But if this is like most databases where there are
far more SELECTS, it probably would hurt processing in the long run.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================