Deleting information from a mySQL database

Anon

Ok, lets say I have a database named forums. Each table is a different category under the main message board. Within each category users can make posts and reply to them. How would I go about deleting posts after a certain amount of time. I know it has to do with the timestamp on that particular row. Any help would be greatly apprecieted.

Anon

Whyever would you have separate tables for each category? Ah, well ...

$tablename is the name of the table
$timestamp is the name of the database column containing the timestamp
$killdate is the timestamp value of the oldest message you want to keep.

DELETE * from $tablename where $timestamp < $killdate

Anon

I think he probably has different tables for each category is because of speed issues. Maybe the table gets too big if you squeeze say 2000000 records into one and might behave better if you have it distributed into different tables... true?

Steve Yelvington wrote:

Whyever would you have separate tables for each category? Ah, well ...

$tablename is the name of the table
$timestamp is the name of the database column containing the timestamp
$killdate is the timestamp value of the oldest message you want to keep.

DELETE * from $tablename where $timestamp < $killdate

Anon

You'd have to do a DELETE for each table.

Say if you want to delete any post more than 30 days old, use something like this:

DELETE FROM your_table WHERE timestamp_column < NOW() - INTERVAL 30 DAY;

Adapt to your needs, read the manual.

---John Holmes...

Anon

As has been said: "Premature optimization is the root of all evil." Database programs are designed to handle huge datasets, and if you have performance problems it's probably due to not having indexed a column that should be indexed.

When you have separate tables for each forum, you have not only cluttered up your data environment, you have interfered with obvious functionality, such as moving a message from one context to another, directly retrieving a specific message, searching all messages globally, counting the current number of messages on the BBS system ... and for what? Insurance against the day you get two million messages on a single topic?

There is a good (heavy, but good) column on database normalization and design techniques here:

http://www.phpbuilder.com/columns/barry20000731.php3

Anon

Thanks for your help guys!