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