Mysql performance.

[daydreamer]

I am building a website which deals with sending messages.

 

At the moment my database has a table which stores these messages:

 

ID(pk), member_id, number, message, date.

 

On one of the pages my site allows the users to see the messages they have sent, and also delete them.

 

When they are deleted, I want to keep them in the database, but not show them on the users 'message page'.

 

 

Which way would be faster/best? To have a table identical to the one above, but when the user deletes them, just delete them from one table.

 

Or put in another column, "deleted", 0=show message, and 1=do not show message.

 

 

[beedie]

I would benchmark it but my guess is the latter as no need to do anything to more than one table. Inserting updating etc for your other querys would involve two tables not one.

[fenway]

Much easier just to flag them.

[JonnoTheDev]

I find it better when using an ENUM field to use 1 & 2 rather that 0 & 1 as the code will see 0 as empty i.e.

// $flag == 0
if(!$flag) {
// display
}
// $flag == 1
else {
dont display
}

[fenway]

If you use ENUM, you can refer to the actual values, not just their bit value.