I browsed the source in order to look a bit at the queries and see what indexes could be added I did NOT profile the code AT ALL

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

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

HTH