On Donnerstag 28 Mai 2009 Maxim Podorov wrote:
> CREATE INDEX dbmail_headervalue_2 ON dbmail_headervalue USING btree
> (emailname);
> CREATE INDEX dbmail_headervalue_3 ON dbmail_headervalue USING btree
> (emailaddr);

We've had this issue in 2.2.x already. We could make an index of 
substring(emailaddr,0,255), but then
a) you can't search the whole string, so a search is incorrect
b) every statement accessing that table would always need 
substrint(emailaddr,0,255) instead just emailaddr in order to use that 
index at all

But I think I have a better idea: If we have a headervalue >255 chars, 
split it at a reasonable point. Example:
From: verylongrecipientl...@myotherdomainsaremuchlargeralso.com, 
verylongrecipientli...@myotherdomainsaremuchlargeralso.com, 
verylongrecipientl...@myotherdomainsaremuchlargeralso.com, 
verylongrecipientl...@myotherdomainsaremuchlargeralso.com, 
Then just split that and INSERT two or more rows into 
dbmail_headervalue, each with the same fields just the emailaddr split 
at a comma < 255 chars (MySQL just support 255 char wide indices, but 
maybe for PostgreSQL we could do the split at 2048 to have less 
entries). Like this, 
a) we can use the normal index
b) a search will be fast and correct in result
It needs a lot more brain power to program this once, but I think it's a 
huge benefit over the currect (2.2.x) situation.

On Donnerstag 28 Mai 2009 Jonathan Feally wrote:
> From the error message below, it looks like we could use a size
> of 2048.

MySQL can only index 255 chars :-(

mfg zmi
-- 
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0660 / 415 65 31                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net                  Key-ID: 1C1209B4

Attachment: signature.asc
Description: This is a digitally signed message part.

_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev

Reply via email to