Whether to delete unnecessary records in the database or to leave?

Question

MySQL

There is a bulletin board and a constant movement of records in the database - ads are removed or hidden.

The essence of the question is how to do it correctly:
1. delete ads (404 or 410 error)
2. or save the ad, giving 200 code, but hide contact details and write something like "Ad is not active"?

If the latter, then how to do it correctly, given that the base will constantly grow, and the index on the enum 'delete', 'active' is unlikely to help?

Answer 1

I would do the following:
1. create another table with an identical structure
2. transfer from the main table to the archive when deleting
Advantages:
1. the main table remains compact
2. you can always restore data if necessary

Answer 2

What's the point of leaving them? They're just useless trash. But in terms of implementation, you can make the expiration date for restoring an expired ad 7 days. If it is not restored, it is removed from the database.

Answer 3

The crux of the matter is how to do it right:

2, in the sense leave, as for any site - the reference mass will not be superfluous.

If the latter, then how to do it correctly, given that the base will constantly grow, and the index on the enum 'delete', 'active' is unlikely to help?

How will it affect the sample at all? It is still present in your select, what's the difference? A direct request for a specific declaration does not use this index, but the list select uses it anyway, no matter how many records you have there.