I'm not a wizard at MySQL (or SQL in general) so let me know if these
indexes look kosher:

On Sep 11, 2007, at 9:20 AM, Mariano Absatz wrote:

On 9/11/07, Dada Mail (Justin Simoni) dada@PROTECTED wrote:

CREATE TABLE dada_settings ( list varchar(16), setting varchar(64), value text ); index on list, maybe anyway, I don't think queries on this table are that frequent and the table itself is short (less than 200 records per mailing list)

CREATE INDEX dada_settings_list_index ON dada_settings (list);

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

This is the key table I think the 4 fields should be indexed most queries have conditions on two or three of the 'list*' fields and some are ORDERed BY email and, since this is probably the largest table (in terms of number of records), this is probably the most influential table when making performance decisions

CREATE INDEX dada_subscribers_all_index ON dada_subscribers (email,
list, list_type, list_status);

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

here we should index by list and archive_id

CREATE INDEX dada_archives_list_archive_id_index ON dada_archives
(list, archive_id);

CREATE TABLE dada_sessions ( id CHAR(32) NOT NULL PRIMARY KEY, a_session TEXT NOT NULL ); I don't think we should index a_session (the place to confirm this would be the CGI::Session documentation)

Yeah, me neither :)

Like I said, I'm an SQL fool, but if these look good, I'll attempt to
port the indexes to Postgres and SQLite and commit the changes to CVS

I'm a little itchy to get a release out Anyone up to have a new
release out? :)