I'm writing an apple iphone application in which the user receives multiple messages from different customers. These messages are saved within an sqlite3 database. As time passes the consumer might prefer to remove received messages in one user, but without a doubt he'll still receive new messages from that user after removing that old ones.

Since locating the messages is going to be done utilizing a SELECT statement, which scenario 's better to use once the user want to remove the messages (when it comes to performance):

  1. DELETE all of the old messages normally and then retrieve they utilizing a statement like: SELECT Messages FROM TableName WHERE UserID = (?)
  2. Give a area towards the table of type INTEGER and upon the DELETE request set this area to at least one and then retrieve the brand new messages utilizing a statement like: SELECT Messages FROM TableName WHERE UserID = (?) AND IsDeleted = 0

Yet another factor, if scenario 1 can be used (normal DELETE) will this cause any fragmentation from the database file around the disk?

Thank you ahead of time.

It might be quite a lousy database if DELETE did not work nicely. In lack of evidence on the contrary, I'd assume you're safe to remove normally. The database's entire raison d'ĂȘtre would be to make these kinds of procedures efficient.

IMHO if you do not use Remove, before long the db can get larger and larger, thus making each Choose much less efficient. well, i figure that removing rows that should never be remade is much more efficient.

Using scenario 1 is way better, since both SELECT and DELETE in SQL operate in the same degree of speed and scenario 1 will grant explore getting dangling tuples (Undesirable Rows) inside your database.

If you're wanting to do data backup after any deletion process so scenario 2 is essential however, you have to consider the growing size your database which results in a reduced performance later on.

Finally I must include that carrying out removing procedures on the database wouldn't cause any fragmentation issues because most of databases have fragmentation and optimizing tools within their engines.