I'm still not convinced that over-indexing is harmful in this case. In
general, too many indices is better than too few, and even if the data
fits in memory, an indexed btree search will always be much faster than an
unindexed search. Even if the table only has a few hundred small records,
an indexed search still comes out vastly ahead, in my experience.
But if you can empirically demonstrate that over-indexing signifficantly
slows down the typical (mostly read) workload of DBMail, I'd be most
interested to see it.
Gordan
On Wed, 14 May 2008, Michael Mayer wrote:
Michael Mayer wrote:
An interesting idea for performance improvement of the messages table would
be a PRIMARY KEY consisting of mailbox_idnr and status, as the
Of course, the physmessage_id must be added to that index as well, so that it
is unique (and the primary key really should be unique). As I said, it could
even make sense to leave out the status, as most mailboxes can be sorted and
filtered in memory. So the final idea is to use this setup:
PRIMARY KEY(mailbox_idnr, physmessage_id)
UNIQUE(message_idnr)
message_idnr INT AUTO_INCREMENT
mailbox_idnr INT
Maybe INDEX(unique_id), if you need it for something. Delete all the other
indexes on the messages table.
That's it for today, promised,
Michael
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
