CODEWITHSUNDEEP
mysql
makeee
makeee

Reputation: 2815

Mysql: deleting rows vs. using a "removed" column

I was previously under the impression that deleting rows in an autoincremented table can harm SELECT performance, and so I've been using a tinyint column called "removed" to mark whether an item is removed or not.

My SELECT queries are something like this:

SELECT * FROM items WHERE removed = 0 ORDER BY id DESC LIMIT 25

But I'm wondering whether it does, in fact, make sense to just delete those rows instead. Less than 1% of rows are marked as "removed" so it seems dumb for mysql to have to check whether removed = 0 for each row.

So can deleting rows harm performance in any way?

Upvotes: 5

Views: 1682

Answers (3)

Piskvor left the building
Piskvor left the building

Reputation: 92782

That depends a lot on your use case - and on your users. Marking the row as deleted can help you in various situations:

  • if a user decides "oh, I did need that item after all", you don't need to go through the backups to restore it - just flip the "deleted" bit again (note potential privacy implications)
  • with foreign keys, you can't just go around deleting rows, you'd break the relationships in the database; same goes for security/audit logs
  • you aren't changing the number of rows (which may decrease index efficiency if the removed rows add up)

Moreover, when properly indexed, in my measurements, the impact was always insignificant (note that I wrote "measurements" - go and profile likewise, don't just blindly trust some people on the Internet). So, my advice would be "use the removed column, it has significant benefits and no significant negative impact".

Upvotes: 3

RRStoyanov
RRStoyanov

Reputation: 1172

You just fill the table with more and more records which you don't need. If you don't plan to use them in the future, I don't think you need to store them at all. If you want to keep them anyway, but don't plan to use them often, you can just create a temp table to hold your "removed" records.

Upvotes: 0

Harry Joy
Harry Joy

Reputation: 59670

I don't think deleting rows harm on select query. Normally peoples takes an extra column named deleted [removed in your case] to provide a restore like functionality. So if you are not providing restore functionality then you can delete the row it will not affect the select query as far as I know. But while deleting keep relationships in mind they should also get deleted or will result in error or provide wrong results.

Upvotes: 0

Related Questions