Query performance increase from deleting rows in SQL database?

Question

I have a database with a single table that keeps track of user state. When I'm done handling the row, its no longer necessary to keep it in the database and can be deleted.

Now lets say I wanted to keep track of the row instead of deleting it (for historical purposes, analytics, etc). Would it be better to:

1. Leave the data in the same table and mark the row as 'used' (with an extra column or something like that)

2. Delete the row from the table and insert it into a separate table that is created only for historical purposes

For choice #1, I wonder if leaving the unnecessary rows in the database will start to affect query performance. (All of my queries are on indexed columns, so maybe this doesn't matter?)

For choice #2, I wonder if the constant deleting of rows will end up causing problems such as fragmentation?

Answer 1

Query performance will be better in the long run:

What is happening with forever inserts:

The table grows, indexes grow, index performance (lookup) is decreases with the size of the table, especially insert performance is hurt.

What is happening with delete:

Table pages get fragmented, so the deleted space is not re-used 100% as expected, more near 50% in MySQL. So the table still grows to about twice the size you might expect for your amount of data. The index gets fragmented and becomes lob sided: It contains your new data but also the structure for your old data. It depends on the structure of your data on how bad this gets. This situation however stabilizes at a certain performance. This performance point has 2 benefits:

1) The table is more limited in size, so potential full table scans are faster

2) Your performance is predictable.

Due to the fragmentation however this performance point is not equal to about twice your data amount, it tends to be a bit worse (benchmark it to see yourself). The benefit of the delete scenario is however since you have a smaller data set, that you might be able to rebuild your index once every reasonable period, thus improving your performance.

Alternatives

There are two alternatives you can look at to improve performance:

• Switch to MariaDB: This gains about 8% performance on large datasets (my observation, dataset just about 200GB compressed data)

• Look at partitioning: If you have a handy partitioning parameter, you can create a series of "small tables" for you and prevent logic for delete, rebuild and historic data management. This might give you the best performance profile.