Since I only know mysql, only mysql types are used. Application would be
same on pgsql.
My misplaced bugtrack entry with Arron's note at bottom
-------------------
I went over the dbmail mysql schema for 2.0rc7 and noticed places where
datatype sizes can be safely downgrade by factor of 2 to decrease index
size on those fields and quite a bit of indices use these fields.
Unsigned int has max value of 4294967295 and only 4 bytes per record vs 8
bytes for bigint. Won't make much of a difference but these stuff can add
up with millions of records. Dbmail need all the speed it can get from the
indices.
Please note that I'm assuming the following fileds do not use negative numbers.
aliases
------
1) alias_idnr bigint(21) --> int(11) UNSIGNED
2) client_idnr bigint(21) --> int(11) UNSIGNED
users
------
1) user_idnr bigint(21) --> int(11) UNSIGNED
2) client_idnr bigint(21) --> int(11) UNSIGNED
3) maxmail_size bigint(21) --> int(11) UNSIGNED
4) curmail_size bigint(21) --> int(11) UNSIGNED
mailboxes
------
1) mailbox_idnr bigint(21) --> int(11) UNSIGNED
2) owner_idnr bigint(21) --> int(11) UNSIGNED
subscription
------
1) user_id bigint(21) --> int(11) UNSIGNED
2) mailbox_id bigint(21) --> int(11) UNSIGNED
acl
------
1) user_idnr bigint(21) --> int(11) UNSIGNED
2) mailbox_id bigint(21) --> int(11) UNSIGNED
physmessage
------
1) messagesize bigint(21) --> int(11) UNSIGNED
2) rfcsize bigint(21) --> int(11) UNSIGNED
messages
------
1) mailbox_idnr bigint(21) --> int(11) UNSIGNED
messageblks
------
1) blocksize bigint(21) --> int(11) UNSIGNED
auto_notifications
------
1) auto_notify_idnr bigint(21) --> int(11) UNSIGNED
2) user_idnr bigint(21) --> int(11) UNSIGNED
auto_replies
------
1) auto_notify_idnr bigint(21) --> int(11) UNSIGNED
2) user_idnr bigint(21) --> int(11) UNSIGNED
************
Aaron note:
Sorry, the code has all been written for 64 bit types. I realize that we
might be safe with code that's able to hold larger data than the database
gives us, but it's a really major infrastructure change that probably is
not such a good idea. This would probably be more appropriate as a thread
on the dbmail-dev mailing list.
************
I went over dbmysql.c and saw that each field is returned as string with
the follow function to provide for string to 64bit int conversion.
-------------------
db.c
u64_t db_get_result_u64(unsigned row, unsigned field)
{
const char *tmp;
tmp = db_get_result(row, field);
return (tmp ? strtoull(tmp, NULL, 10) : 0);
}
-------------------
So it would be safe as Arron mentioned and my view is that the database i/o
is and will always be dbmail's bottleneck for large setups and to get the
maxmium speed out it, we should use the smallest size of fields possible
more for indicies sake and less for storage size concerns. Not a good
consideration for the 2.0 release but perhaps part of the major db
optimizations planned for 2.1?
Any other thoughts on this?
Xing
