DELETE FROM `table` or UPDATE `table` SET `deleted` = 1

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

DELETE FROM `table` or UPDATE `table` SET `deleted` = 1

Post by Ollie Saunders »

I've heard people say you shouldn't ever DELETE from a table and should instead have an indexed `deleted` field that you can set to true or false. Is this really a good idea?

Discuss :)
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

I do that for a lot of things... some things I just delete. I like using that method because the applications I build are for users who are sort of "n00bs", so a lot of time they come to me and say "I JUST DELETED EVERY USER FROM THE DATABASE!!! WHAT DO I DO?" It's nice to tell them that I can log in and reset their changes.

For certain things I think it's kind of pointless though.

EDIT: The downside is that you have to remember to do "WHERE DELETED != 1" in all of your queries.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

The downside is that you have to remember to do "WHERE DELETED != 1" in all of your queries.
Yeah I can probably cope with that. Can anyone comment on performance though?
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

I am not aware of performance issues, you just need to add deleted to your indexes. Of course there will be an issue though if you have 10 million records marked as deleted and only 2,000 active records.

I usually only use this method when marking items that need to be referenced again in the future, for instance deleting products when they need to still be listed in an order history. I don't see any other reason to keep records around if they will never again be used.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

I usually only use this method when marking items that need to be referenced again in the future, for instance deleting products when they need to still be listed in an order history. I don't see any other reason to keep records around if they will never again be used.
Hmm yes I agree with that. Perhaps the best thing would be to have a cron that DELETEs the deleted = 1 records after x days but I guess then you need a dateDeleted record and it starts to sound like work.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

This is just a half-baked thought, but what if you combined the two...

Code: Select all

DELETE FROM products WHERE deleted NOT NULL AND deleted > {timestamp}
you probably wouldn't need the NOT NULL but it illustrates my point
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

I think this idea will not be useful for the database with relational tables. Is it right ?
Post Reply