See how many rows have been added to MySQL from the beginning to now (even deleted rows)

I have a python code and a table that add a row and delete it after a few minutes.
I need to see how many row have been added from the beginning until now.

Note : I have a primary key in my table too.
Note : COUNT(*) only returns number of rows

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Queries in MySQL only request the current status of the database, so you cannot use a simple query to count inserted and deleted rows.
The usual solution for this problem is by implementing an archiving mechanism instead of a simple deletion.

So you will have to add a new column in the table to store the fact that the row has been archived, and each time your application has to delete a row it will update this column instead. Then a simple count(*) will count all inserted and archived rows.

Here are a few ideas for the new column (from the simplest to the most useful for future evolution):

• Column name is deleted and it can take 2 values: 0 or 1 (boolean)
• Column name is status and it can take 2 values: “active” or “archived”
• Column name is deleted_at so it is null if the row is still valid, and it contains the datetime of deletion if the row has been archived.

In addition, this solution will empower your application with the “unarchive” ability.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0