I was helping a client with a slow Dada Mail and we were thinking that the SQL table schema could do with an overhaul. Here are the current optimized schemas - most of the changes have been to manage the data type a bit more finely. Any one see any problems? Anyone add any useful indexes?

(MySQL)

CREATE TABLE dada_settings (
list                             varchar(16),
setting                          varchar(64),
value                            text
);

CREATE TABLE dada_subscribers (
email_id                        int4 not null primary key auto_increment,
email                            text(320),
list                             varchar(16),
list_type                        varchar(64),
list_status                      char(1)
);

CREATE TABLE dada_archives (
list                          varchar(32),
archive_id                    varchar(32),
subject                       text,
message                       mediumtext,
format                        text,
raw_msg                       mediumtext
);

CREATE TABLE dada_sessions (
     id CHAR(32) NOT NULL PRIMARY KEY,
     a_session TEXT NOT NULL
  );



(Postgres)

CREATE TABLE dada_settings (
list                             varchar(16),
setting                          varchar(64),
value                            text
);

CREATE TABLE dada_subscribers (
email_id                         serial,
email                            text,
list                             varchar(16),
list_type                        varchar(64),
list_status                      char(1)
);

CREATE TABLE dada_archives (
list                          varchar(32),
archive_id                    varchar(32),
subject                       text,
message                       text,
format                        text,
raw_msg                       text
);


CREATE TABLE dada_sessions (
    id CHAR(32) NOT NULL PRIMARY KEY,
    a_session BYTEA NOT NULL
);

(SQLite)


CREATE TABLE dada_settings (
list                             varchar(16),
setting                          varchar(64),
value                            text
);

CREATE TABLE dada_subscribers (
email_id                         INTEGER PRIMARY KEY AUTOINCREMENT,
email                            text(320),
list                             varchar(16),
list_type                        varchar(64),
list_status                      char(1)
);


CREATE TABLE dada_archives (
list                          varchar(32),
archive_id                    varchar(32),
subject                       text,
message                       mediumtext,
format                        text,
raw_msg                       mediumtext
);

CREATE TABLE dada_sessions (
     id CHAR(32) NOT NULL PRIMARY KEY,
     a_session TEXT NOT NULL
  );

Post:
mailto:dadadev@PROTECTED

Unsubscribe:
http://mojo.skazat.com/cgi-bin/dada/mail.cgi/u/[list]/

List Information:
http://mojo.skazat.com/cgi-bin/dada/mail.cgi/list/[list]

Archive:
http://mojo.skazat.com/cgi-bin/dada/mail.cgi/archive/[list]

Mailing List Powered by Dada Mail