Paul J Stevens wrote:
Michael Mayer wrote:
But especially the messages
table seems to be over-indexed.
Please propose a better index setup.
An interesting idea for performance improvement of the messages table
would be a PRIMARY KEY consisting of mailbox_idnr and status, as the
primary key serves as clustered index in InnoDB. This saves disk I/O and
speeds up the whole thing, because the typical use case is that the user
wants to download all new (that information is stored in status, right?)
messages from it's mailbox. All these messages would be stored in
clustered data pages. It also slows down writing a bit, because the
table has to be reorganized every time.
But, the primary key is also stored in every row of every index, so it
should be very small. IMHO, this is a shortcoming of MySQL at the moment
and I don't see a quick fix. You should be able to define a clustered
index other than the primary key. On the other hand, it might work
anyways, since InnoDB could use it's own 6 byte row id integer, if it
has to deal with multi-part primary keys. This would even save 2 bytes
compared to the current bigint (8 byte) primary key. In the worst case,
you would end up with a 9 byte primary key.
- The general goal is to reduce data page access as much as possible and
keep data structures small. The current index setup leads to a messages
table where 2/3 of the space is occupied by the index and only 1/3 is
data. That means, if data grows to 1 GB, the table requires 3 GB on the
hard disk.
- Every additional index also degrades write performance a lot.
The bottom line is that table optimization heavily depends on
implementation details of the storage engine and the exact use case.
Michael
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
