Hi, I've been looking through ways of optimizing our dbmail setup. Other than rearranging the indexing and reducing the number of queries, I was thinking about moving some of the temporary tables over to the mysql heap(memory) engine type instead of myisam/innodb since the content of these tables aren't permanent and only used short term.
If these tables were in memory it should remove some database i/o overhead from the dbmail-smtp/pop3/imap process. The only down side I can see to using heap is that it doesn't support auto-increment and database data is lost on restart, other than that might make a slight improvement on speed for busy setups. Lots of the tables seem to contain a unique index on a primary key, which seems a bit odd since a primary key needs to be unique by default. A primary key alone should be fine? In some cases messages table has a primary key, key and unique index all on the same row (again surely only a primary key is needed?). Such as the users table there are the following: PRIMARY KEY (user_idnr), KEY user_idnr (user_idnr, userid), UNIQUE useridnr_2 (user_idnr, userid), UNIQUE userid_2 (userid) Surely just PRIMARY KEY (user_idnr, userid) should do fine? I'm by no means a database expert, but just looking towards ways of helping dbmail scale better. Does anyone have any thoughts on this? Simon
