At least in postgresql 8.2.7 it works fine, also returning the messageblk. I got rid of the group by also, because I couldn't think of a case where it would be necessary.
The original query: SELECT m.message_idnr,k.messageblk FROM dbmail_messageblks k JOIN dbmail_physmessage p ON k.physmessage_id = p.id JOIN dbmail_messages m ON p.id = m.physmessage_id WHERE mailbox_idnr = 8 AND status IN (0,1 ) AND k.is_header = '0' GROUP BY m.message_idnr,k.messageblk HAVING ENCODE(k.messageblk::bytea,'escape') LIKE '%sim%' My Query SELECT m.message_idnr,k.messageblk FROM dbmail_messageblks k JOIN dbmail_physmessage p ON k.physmessage_id = p.id JOIN dbmail_messages m ON p.id = m.physmessage_id WHERE mailbox_idnr = 8 AND status IN (0,1 ) AND k.is_header = '0' and ENCODE(k.messageblk::bytea,'escape') LIKE '%sim%' Josh Berkus wrote: > Paul, > >>> 2) Wouldn't the query be much faster if the >>> ENCODE(k.messageblk::bytea,'escape') LIKE '%sim%' was in the WHERE >>> clause instead of the HAVING clause? >> >> Try it. Afaik, it simply wont work. > > That depends on whether you're trying to return just a list of IDs, or > the full contents of the messages. If you just need a list of IDs, you > can use the WHERE clause. > > --Josh > > _______________________________________________ > DBmail mailing list > [email protected] > https://mailman.fastxs.nl/mailman/listinfo/dbmail _______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
