If you have a board that is storing a large number of personal messages (mine has over 300k messages) then deleting a PM will often take a surprisingly long time for what would apparently be a very simple action.
On my site, which is a pretty decent dedicated Linux box (4 GB RAM, 2 x 2.2 GHz Opteron 175 CPU), the deletion of a single PM can often take 15 seconds or more.
This problem was mentioned previously on invisionpower.com here: Slow Query on IPB 2.2.1 (topic closed without discussion)
It has also been encounted by another large forum and their solution was to delete all old PMs: All PMs older than December 2006 deleted
Of course deleting all old PMs, or limiting the number of PMs available to members, is not really a solution to the problem. In software you don't fix a problem by disabling the feature that causes it.
The problem query is this one: select msg_id from ipb_message_text where msg_deleted_count >= msg_sent_to_count
11 seconds, rows sent 1, rows examined: 339984
This is located in sources/lib/func_msg.php at line 660 (in IPB v. 2.3.5) within the delete_messages() function:
$this->ipsclass->DB->simple_construct( array( 'select' => 'msg_id', 'from' => 'message_text', 'where' => 'msg_deleted_count >= msg_sent_to_count' ) );
The problem appears to be that the code is examining the entire message text table.
This could possibly be speeded up by modifying the code to add a second where clause as follows:
$this->ipsclass->DB->simple_construct( array( 'select' => 'msg_id', 'from' => 'message_text', 'where' => 'msg_deleted_count >= msg_sent_to_count AND msg_id IN (' . implode(',',$final_ids) . ')' ) );
Any thoughts?
