How can I fix this scaling issue while gently deleting items?

Question

I have a database where most tables have a delete flag for tables. Thus, the soft system deletes items (therefore, they are no longer available, unless by administrators)

What bothers me, after a few years, when the tables are much larger, it is that the overall system speed will be reduced.

What can I do to counteract these effects.

• Am I indexing a delete field?
• Do deleted data be moved to and from the identical deletion table during recovery?
• Have I shared data across multiple MySQL servers over time? (based on growth)

I would be grateful for any suggestions and stories.

UPDATE:

Thus, separation seems to be the key to this. But not partitioning will simply create two “tables”, one with deleted items and one without deleted items.

Thus, over time, the deleted partition will become large, and random selections from it will be slow (and slower over time).

Will there be a speed difference that I should worry about? Since I retrieve most (if not all) of the data for some key values ​​(some of them do a search, but they can be slow for this setting)

Answer 1

I would split the table into a flag DELETE.

, SQL .

Answer 2

, , . , ? , , .

Answer 3

, , . , , .. , ( , ) , .

, , , , / . , , "" , , ( ) ( ) , . , , , , , (, ) . , , , user_id ( transaction_id ), , ( , , -offs, ).

, . ( , , "" "" ), , , . , , . . - history_history, n , , . . "" , , n , , , (, - , , 30 , .)

, user_id - ​​. . , , (, forum_posts , sales_records), FAR . user_id, google; -]

. , , .