Paul J Stevens wrote:
Jonathan Feally wrote:
The emailaddr column is used for sorting only. The headervalue column is
what is used for the where clauses.
Jon, in your latest code, the emailaddr/emailname columns are never used
again after insertion. We really shouldn't be storing information that
is not used, especially when they are blobs.
Also, the sortfield is a blob/text field but stored as a string. That is
incorrect. It should use the libzdb blob interfaces, like done for
headervalue. But read on below.
In fact, I'm starting to believe we should simply drop the
emailaddr/emailname cache columns, and rely on headervalue and sortfield.
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.
We should truncate the sortfield to 255 characters, change it to a
varchar(255) and do full width indexing. Using the first 255 characters
of a field to sort seems like sufficient for the forseable future. Do we
really want to be able to sort on longer headers?
The above all sounds fine. I had the email fields because we had it like
that in the old tables. But since we don't need it for anything - lets
get rid of it as I see you already have. Sending you a patch for the
create_tables that was missed in those changes.
-Jon
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
>From 6bc44938089194e4d652cd67ba7ed39c8da083c3 Mon Sep 17 00:00:00 2001
From: Jonathan Feally <vult...@netvulture.com>
Date: Sun, 31 May 2009 18:25:32 -0700
Subject: [PATCH] Fix mysql create_tables missed table change
---
sql/mysql/create_tables.mysql | 8 ++------
1 files changed, 2 insertions(+), 6 deletions(-)
diff --git a/sql/mysql/create_tables.mysql b/sql/mysql/create_tables.mysql
index b293cad..324e8a8 100644
--- a/sql/mysql/create_tables.mysql
+++ b/sql/mysql/create_tables.mysql
@@ -137,16 +137,12 @@ CREATE TABLE `dbmail_headervalue` (
`id` bigint(20) NOT NULL auto_increment,
`hash` varchar(255) NOT NULL,
`headervalue` text NOT NULL,
- `emailname` text,
- `emailaddr` text,
- `sortfield` text,
+ `sortfield` varchar(255) default NULL,
`datefield` datetime default NULL,
PRIMARY KEY (`id`),
KEY `hash` (`hash`),
KEY `headervalue` (`headervalue`(255)),
- KEY `emailname` (`emailname`(255)),
- KEY `emailaddr` (`emailaddr`(255)),
- KEY `sortfield` (`sortfield`(255)),
+ KEY `sortfield` (`sortfield`),
KEY `datefield` (`datefield`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
1.6.1.3
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
