Mark Mackay - Orcon wrote:
Suppose we stored the headers in a separate table and used the
database indexing facilities.
We could then do without a separate header cache, by just using
well formulated queries.
I did that previously, and the speed hit was quite profound each time
someone did a search. I ended up writing a perl-script to batch-process and
generate my own table cache every five minutes and when someone logged into
webmail. Worked better, but still was icky.
Well, I believed you could be helped by a fulltext index but I
might be wrong.
A few questions,
- What should the table structure be?
- What about automatic rebuilding the table cache, if you get a
query for a new header?
- What about this table structure
CREATE TABLE cached_header_types (
header_idnr int(??) NOT NULL AUTO_INCREMENT,
header_name text,
PRIMARY KEY (header_idnr),
INDEX (header_name)
) TYPE=InnoDB;
This table holds the headers types we cache,
CREATE TABLE header_cache (
header_cache_idnr bigint(21) NOT NULL AUTO_INCREMENT,
header_type int(??),
header_val text,
message_id bigint(21),
UNIQUE(message_id, header_type),
FOREIGN KEY (`message_id`)
REFERENCES `messages` (`message_idnr`)
ON DELETE CASCADE,
) TYPE=InnoDB;
This way, we have a good index on the user/header combination,
This might not be the best approach,
I beleive the typical query is
Get headers A,B and C from mailbox x.
This would reguire something like
SELECT header_type, header_val FROM (header_cache, messages)
WHERE header_cache.message_id=messages.message_idnr AND
messages.mailbox_idnr=<XXX> AND
(header_cache.header_type=head_id1 OR
header_cache.header_type=head_id2 OR ...
header_cache.header_type=head_idN)
I beleive we would get a quite good index and quite fast replies.
The trick is just,
How do you automagically rebuild the header table?
This may also be a source for DOS attacks but, if you are able to
get this kind of access to the mail system, you will for shure be
able to DOS it some way or another.
/Magnus
