#10 search faster

[Casper Langemeijer]

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.

[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.