Need help with a MySQL-problem (PHP)

[Kennel]

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?

[oblig]

Suppose you could try DELETE LOW_PRIORITY FROM. That would make the query wait a while (until other queries are complete).

You could also split the array into chunks, and add something like an OR into the query to combine the two originals.

[Kennel]

Thanks for you response. I split it up a bit. I don't know how the speed is since I only have a few hundred posts to try it on (on these posts it's instant though). This is the way I do it now. Any recommendations?

    // Set the board in maintenance mode
    mysql_query( "UPDATE options SET maintenance='yes'" ) or error( mysql_errno(), __LINE__, mysql_error() );
    
    // Select the thread ID's (parentid=0)
    $result = mysql_query( 'SELECT id FROM messages WHERE parentid=0 and lastpost < UNIX_TIMESTAMP()-('.intval( $pruneseconds ).')' ) or error( mysql_errno(), __LINE__, mysql_error() );
    $numthreads = mysql_num_rows( $result );
    $threadcount = 0;

    if( $numthreads > 0 )
    {
        do
        {
            $threads = NULL;
            for( $tempcount = 0; $threadcount < $numthreads; $threadcount++ )
            {
                if( $tempcount == 8 )    // We only do 8 threads at a time. Otherwise the query would be "too long".
                    break;
                $threads[] = mysql_result( $result, $threadcount );
                $tempcount++;
            }
    
            if( $threads == NULL )    // We're done
                break;

            $threadstring = implode( ', ', $threads );
            mysql_query( 'DELETE FROM messages WHERE id IN( '.$threadstring.' ) OR parentid IN( '.$threadstring.' )' ) or error( mysql_errno(), __LINE__, mysql_error() );
    
        } while( mysql_affected_rows() > 0 );

        mysql_query( 'OPTIMIZE TABLE messages' ) or error( mysql_errno(), __LINE__, mysql_error() );
    }
    else
        echo 'nothing to prune';

    mysql_query( "UPDATE options SET maintenance='no'" ) or error( mysql_errno(), __LINE__, mysql_error() );