The emailaddr column is used for sorting only. The headervalue column is what is used for the where clauses. So the only column that needs to be worried about for the index containing the data is the headervalue. In most cases a 255 byte index on the emailaddr column would be sufficient for sorting messages on the server side. The emailname is just there for future use. We cannot split the recipients into multiple rows as that would break the imap sort back to way it was where some messages could be sorted in a different order than the rfc permits.
Easier eplained as Msg 1 to: c, a Msg 2 to: b Msg 3 to: c, b If we had 5 rows of the above recipients, then Msg 1 would be sorted ahead of Msg 2, but the rfc says we can only look at the first recipent, so really we should be sorting based on b, then c. So - in retrospect, I need to change the behavior so that messages are only sorted by the 1st recipient, that way other field being sorted upon can influence the result set properly. I'll get to working on a patch for just this thing and fix the indexes. -Jon Michael Monnerie wrote: > 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 > > ------------------------------------------------------------------------ > > _______________________________________________ > Dbmail-dev mailing list > Dbmail-dev@dbmail.org > http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev > -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. _______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
