Gmail Mysql Database Design:

This is the new design as of 0.4.8pre1. It has been stable on this for a long time now. See below for a list of possible changes I am stacking up to make sometime. Changing the database format is really a chore because it is pretty tough (but not impossible) to export all the data and import it again.

The basic design in that the mail data is not stored in one table but two. The difference is that one of them is a fixed length table, and the other is a dynamic length table. Fixed length tables are MUCH MUCH faster than the dynamic ones, so it is worth splitting them up. To display the contents of a vfolder (eg: date, subject, fromfield, matched) everything is in the fixed length table.

The name of the fixed length table is 'display' and the name of the dynamic length table is 'details'.

The basic email message can be reconstructed by concatenating details.headers and details.message. All the other fields are redundant data that has been parsed out for use in vfolder queries.

In the design of the tables there is a tradeoff between parsing out special fields at download time or parsing them at runtime/display time. The advantage of parsing out special fields at download time means that we can have special fields to use in vfolder queries. The disadvantage is that we got lots of new fields and complicate the database design.

eg: we could parse the subject at runtime (from display.headers) but then we could not use the headers field in vfolders (not easily anyway). We do not parse attachments at download time, as you would never search on it.


Display:
This is a fixed length table.
+------------+--------------------------------------+------+-----+---------------------+----------------+
| Field      | Type                                 | Null | Key | Default             | Extra          |
+------------+--------------------------------------+------+-----+---------------------+----------------+
| id         | mediumint(11) unsigned               |      | PRI | 0                   | auto_increment |
| date       | datetime                             |      | MUL | 0000-00-00 00:00:00 |                |
| subject    | char(255)                            |      | MUL |                     |                |
| fromfield  | char(255)                            |      | MUL |                     |                |
| readstatus | enum('Read','Unread', 'Queued', 'Sent')                |      | MUL | Read                |                |
| direction  | enum('Incoming','Outgoing') |      | MUL | Incoming            |                |
| uid        | char(255)                            |      | MUL |                     |                |
| matched    | set(' ')                 |      | MUL | False               |                |
+------------+--------------------------------------+------+-----+---------------------+----------------+

The matched field get built around the names of the user vfolders. When you add/remove vfolders you should rebuild the matched index (see advanced menu). That is, gmail will modify the 'set' to contain the names of all your vfolders. The set is stored in a binary way, so we can use the 'matched' field for fast caching of vfolders. Read the online help for more info, then look at the code.


Details:
This is a dynamic length table.
+-------------+--------------+------+-----+---------------------+----------------+
| Field       | Type         | Null | Key | Default             | Extra          |
+-------------+--------------+------+-----+---------------------+----------------+
| id          | int(11)      |      | PRI | 0                   | auto_increment | unique index
| headers     | text         |      |     | NULL                |                |
| message     | text         |      |     | NULL                |                |
| tofield     | text         |      |     | NULL                |                |
| ccfield     | text         | YES  |     | NULL                |                |
| bccfield    | text         | YES  |     | NULL                |                |
| attachments | text         | YES  |     | NULL                |                |
+-------------+--------------+------+-----+---------------------+----------------+

NOTES:
We don't use the attachments field as we parse them at run time. Drop it.
We should really have split the headers and the message field, as we could do that at run time. Although it is worth having the headers seperate for vfolder queries...
Change the status fields (like direction) to be BOOLEAN types. eg: rename direction to 'incoming' and incoming will either be true or false. This will use much less space than an enum, and run faster too.
Other status fields to add:
	- splic read status into 'read' (boolean), and a sentstatus enum ('Received', 'Queued', 'Sent').
	- Add another date field for 'datedownloaded'.

