Changeset 419

Show
Ignore:
Timestamp:
2011-02-27 09:54:49 (1 year ago)
Author:
hannes
Message:

some performance improvements for the messenger

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • trunk/classes/pages/Messenger.php

    r417 r419  
    4242        // get bans 
    4343        require_once('includes/config/bans.php'); 
    44         // flush deleted messages 
    45         $q = $C->prepare('DELETE FROM ' . $SETTINGS['dbtableprefix'] . 'messages WHERE messagesenderflag=:zero1 AND messagereceipientflag=:zero2'); 
    46         $q->bindValue(':zero1', 0, PDO::PARAM_INT); 
    47         $q->bindValue(':zero2', 0, PDO::PARAM_INT); 
    48         $q->execute(); 
    49         $q = NULL; 
    5044        // container 
    5145        $div = $this->html->body->addChild('div'); 
    …… 
    7670                if (isset($INPUT['method']) && ($INPUT['method'] == 'receipient' || $INPUT['method'] == 'sender')) { 
    7771                    if (isset($INPUT['delete']) && is_array($INPUT['delete']) && count($INPUT['delete']) > 0) { 
     72                        $msg_ids = Array(); 
    7873                        foreach ($INPUT['delete'] as $key=>$val) { 
    7974                            if ($val == 1) { 
    80                                 // check if this user is even allowed to perform this action 
    81                                 $q = $C->prepare('SELECT message' . $INPUT['method'] . ' FROM ' . $SETTINGS['dbtableprefix'] . 'messages WHERE messageid = :id'); 
    82                                 $q->bindParam(':id', $key, PDO::PARAM_INT); 
    83                                 $q->execute(); 
    84                                 $member = $q->fetchColumn(); 
    85                                 $q = NULL; 
    86                                 if ($member == $_SESSION['memberid']) { 
    87                                     // set flag to 'deleted' 
    88                                     $q = $C->prepare('UPDATE ' . $SETTINGS['dbtableprefix'] . 'messages SET message' . $INPUT['method'] . 'flag = :zero WHERE messageid = :id'); 
    89                                     $q->bindValue(':zero', 0, PDO::PARAM_INT); 
    90                                     $q->bindParam(':id', $key, PDO::PARAM_INT); 
    91                                     $q->execute(); 
    92                                 } 
    93                             } 
    94                         } 
     75                                $msg_ids[] = $key;   
     76                            } 
     77                        } 
     78                        $q = $C->prepare('UPDATE ' . $SETTINGS['dbtableprefix'] . 'messages SET message' . $INPUT['method'] . 'flag = :zero WHERE messageid IN (' . join(', ', $msg_ids) . ') AND message' . $INPUT['method'] . ' = :memberid'); 
     79                        $q->bindValue(':zero', 0, PDO::PARAM_INT); 
     80                        $q->bindParam(':memberid', $key, PDO::PARAM_INT); 
     81                        $q->execute(); 
    9582                    } 
    9683                    $div->addChild('div', $LANG['message_deleted']); 
    …… 
    509496            break; 
    510497        } 
     498        if ($INPUT['action'] == 'delete' || $INPUT['action'] == 'batchdelete') { 
     499            // flush deleted messages 
     500            $q = $C->prepare('DELETE FROM ' . $SETTINGS['dbtableprefix'] . 'messages WHERE messagesenderflag=:zero1 AND messagereceipientflag=:zero2'); 
     501            $q->bindValue(':zero1', 0, PDO::PARAM_INT); 
     502            $q->bindValue(':zero2', 0, PDO::PARAM_INT); 
     503            $q->execute(); 
     504            $q = NULL; 
     505        } 
    511506    } /* constructor */ 
    512507 

  • trunk/db/mysql.sql

    r415 r419  
    164164CREATE INDEX idx_messagesender_messagetime ON %prefix%messages (messagesender ASC, messagetime DESC); 
    165165CREATE INDEX idx_messagereceipient_messagetime ON %prefix%messages (messagereceipient ASC, messagetime DESC); 
     166CREATE INDEX idx_messages_receipient_flag_time ON %prefix%messages (messagereceipient ASC, messagereceipientflag DESC, messagetime DESC); 
     167CREATE INDEX idx_messages_sender_flag_time ON %prefix%messages (messagesender ASC, messagesenderflag DESC, messagetime DESC); 
    166168CREATE INDEX idx_moderators_forum ON %prefix%moderators (forum ASC); 
    167169CREATE INDEX idx_moderators_member ON %prefix%moderators (member ASC); 

  • trunk/db/postgresql.sql

    r415 r419  
    164164CREATE INDEX idx_messagesender_messagetime ON %prefix%messages (messagesender ASC, messagetime DESC); 
    165165CREATE INDEX idx_messagereceipient_messagetime ON %prefix%messages (messagereceipient ASC, messagetime DESC); 
     166CREATE INDEX idx_messages_receipient_flag_time ON %prefix%messages (messagereceipient ASC, messagereceipientflag DESC, messagetime DESC); 
     167CREATE INDEX idx_messages_sender_flag_time ON %prefix%messages (messagesender ASC, messagesenderflag DESC, messagetime DESC); 
    166168CREATE INDEX idx_moderators_forum ON %prefix%moderators (forum ASC); 
    167169CREATE INDEX idx_moderators_member ON %prefix%moderators (member ASC); 

  • trunk/db/sqlite.sql

    r415 r419  
    164164CREATE INDEX idx_messagesender_messagetime ON %prefix%messages (messagesender ASC, messagetime DESC); 
    165165CREATE INDEX idx_messagereceipient_messagetime ON %prefix%messages (messagereceipient ASC, messagetime DESC); 
     166CREATE INDEX idx_messages_receipient_flag_time ON %prefix%messages (messagereceipient ASC, messagereceipientflag DESC, messagetime DESC); 
     167CREATE INDEX idx_messages_sender_flag_time ON %prefix%messages (messagesender ASC, messagesenderflag DESC, messagetime DESC); 
    166168CREATE INDEX idx_moderators_forum ON %prefix%moderators (forum ASC); 
    167169CREATE INDEX idx_moderators_member ON %prefix%moderators (member ASC);