Geek Answers Handbook

Mysql: deleting rows compared to using a "deleted" column

I used to have the impression that deleting rows in a table with auto-increments could hurt SELECT performance, so I used a tinyint column called "remove" to indicate whether the item was deleted or not.

My SELECT queries look something like this:

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

But I wonder if it really makes sense to just delete these lines. Less than 1% of the rows are marked as "deleted", so it seems that for mysql it seems like you need to disable = 0 for each row.

So can in any way delete rows that affect performance?

+4
source share
3 answers

It depends on your use case - and on your users. Marking the deleted line can help you in various situations:

  • if the user decides “oh, I need this item in the end”, you don’t need to go through a backup to restore it - just flip the “deleted” bit again (note the potential privacy consequences)
  • with foreign keys, you cannot just delete rows; you are breaking relations in the database; the same goes for security / audit logs.
  • you do not change the number of rows (which can reduce the efficiency of the index if the deleted rows add up)

In addition, with the correct indexing in my measurements, the impact was always insignificant (note that I wrote “measurements” - go and profile in the same way, do not just blindly trust some people on the Internet). So my advice would be to “use the removed column, it has significant advantages and does not have a significant negative impact.”

+3
source

I do not think that deleting harm lines for the selected request. Usually people take an extra column called deleted [deleted in your case] to provide recovery . Therefore, if you do not provide a recovery function, you can delete a row that does not affect the query of choice, as far as I know. But when deleting to keep the relationship in mind, they must also be deleted or result in an error or provide incorrect results.

0
source

You simply fill out the table with more and more entries that you do not need. If you do not plan to use them in the future, I do not think that you need to store them at all. If you want to save them anyway, but do not plan to use them often, you can simply create a temporary table to store your "deleted" records.

0
source

More articles:


All Articles