Need help with a MySQL-problem (PHP)
I'll try to explain my problem as good as possible.
I'm writing a forum software using PHP/MySQL. I want to add a prune feature for administrators and this is where I'm stuck. I let the administrator enter how old threads have to be to be pruned and then I have to do the actual pruning.
I have a table called messages. In this table both regular messages and messages that are topics exist. The rows in this table have a field called parentid and for topics this field is 0 and for regular messages it is the id of the topic in which they reside. The table also has a field called lastpost which is NULL for all regular messages, but is set for topics. Whenever someone posts to a thread I update the lastpost in the "parent message". This way, selecting the threads that are to be pruned is easy.
SELECT id FROM messages WHERE lastpost < (UNIX_TIMESTAMP()-$pruneTime)
This way I get a nice PHP array with the id's of the threads that are to be pruned, but this is as far as I get.
I browsed the MySQL documentation and ran across subselects and considered using somethins like this:
DELETE FROM messages WHERE id IN( <implode the array here (comma separated)> )
and then something similar for the messages within those threads
DELETE FROM messages WHERE parentid IN( <implode the array here (comma separated)> )
(could these two queries be merged into one?)
but then I realised that if there are a LOT of messages in the database the query would be very, very long (we're talking megabytes). I also considered the fact that this operation could be very demanding for the database and that it perhaps would "tie up" the forum completely for some time. Perhaps even so demanding that the PHP script would time out.
Do you guys have any idea how this could be done in a smart way?