#10 search faster

open
nobody
None
5
2006-07-26
2006-07-26
No

function GetSearchList in includes/db.php does a
*very* slow search.

It requests all mails, and in PHP filters them.

I like to create a query where the search keyword are
in, and let MySQL do the math. That is much more
efficient.

Discussion

  • Casper Langemeijer

    Logged In: YES
    user_id=1053109

    I created a query for function GetSearchList:

    $sql = "
    SELECT DISTINCT `dbmail_messages`.`unique_id`
    FROM `dbmail_messageblks`
    LEFT OUTER JOIN `dbmail_messages` ON
    `dbmail_messageblks`.`physmessage_id`=`dbmail_messages`.`ph
    ysmessage_id`
    LEFT OUTER JOIN `dbmail_mailboxes` ON
    `dbmail_messages`.`mailbox_idnr`=`dbmail_mailboxes`.`mailbo
    x_idnr`
    LEFT OUTER JOIN `dbmail_physmessage` ON
    `dbmail_messages`.`physmessage_id`=`dbmail_physmessage`.`id
    `
    WHERE `dbmail_mailboxes`.`owner_idnr`=".$_SESSION['user']-
    >userData['user_idnr']."
    AND `dbmail_messages`.`deleted_flag`=0 AND
    `dbmail_messages`.`status`<2
    AND (
    ( `dbmail_messageblks`.`is_header`= 0 AND
    `dbmail_messageblks`.`messageblk` LIKE '%".$term."%')
    OR
    ( `dbmail_messageblks`.`is_header`= 1 AND
    (
    `messageblk` REGEXP 'From: [^\n]*".$term."'
    OR
    `messageblk` REGEXP 'Subject: [^\n]
    *".$term."'
    )
    )
    )
    ORDER BY `dbmail_physmessage`.`internal_date` DESC";

    ExecuteSQL($sql, $rs);

    $match = array();
    while($n = SQLFetchArray($rs)){
    $match[] = $n['unique_id'];
    }

     
  • Borvik Bramblewood

    Logged In: YES
    user_id=1453793

    I would love to do that too - the difficulty with that was
    with one of the servers I had this set on, searching wasn't
    going so well as the field type was longblob.

    Maybe I can implement this in the AJAX version I'm working on.

     
  • Borvik Bramblewood

    Logged In: YES
    user_id=1453793

    There is another problem - besides longblob, what about
    messages that are base64 encoded - they need to be decoded
    first.

     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks