MyBB Community Forums

on my site I have more than 200,000 records in privatemessages table.

and in private.php we have the following SQL statement, which cause full scan of the mentioned table

// Attempt to see if this PM is a duplicate or not
$time_cutoff = TIME_NOW - (5 * 60 * 60);
$query = $db->query("
        SELECT pm.pmid
        FROM ".TABLE_PREFIX."privatemessages pm
        LEFT JOIN ".TABLE_PREFIX."users u ON(u.uid=pm.toid)
        WHERE u.username='".$db->escape_string($mybb->input['to'])."' AND 
             pm.dateline > {$time_cutoff} AND pm.fromid='{$mybb->user['uid']}' AND
             pm.subject='".$db->escape_string($mybb->input['subject'])."' AND
             pm.message='".$db->escape_string($mybb->input['message'])."' AND 
             pm.folder!='3'
        ");

in peak times this SQL takes up to 50 seconds.

just by adding index on privatemessages.toid field I reduce reads from 200,000 to less than 100.
that mean reducing work time (in my hardware and system configuration in non-peak time) from 0.6 seconds to 0.0006 seconds.

maybe it is a good idea to include this index to standard distribution.

I agree. There should always be an index on any field that is regularly used in a where or join statement

Indeed, we should probably add an index to toid for MyBB 1.6

Funny I just did this at HF because my tracking page took a minute and a half to load.

I have a 1gb PM table with about 1.5 million records.

The whole tracking page needs pagination too. It's one giant page if you have a large forum and lots of PMs.