FullText Search for Emails

[sylvester dsouza]

We have an application that stores emails in a mysql database (MYISAM).
The mail body is parsed and stored in a table with the following structure.

+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(15) | NO | PRI | | |
| mbody | mediumtext | NO | MUL | | |
+-------+------------+------+-----+---------+-------+

Indexing on the mbody column is terrible because of the binary content in the mime encoded email bodies.

We need to have some kind of indexing on this table to provide email search options to the users.

Considered Sphinx as an option, but the problem is
1) We have 200-300 databases with the same tables that need to be indexed.
2) Some databases have over 1000000 emails.
3) Need a mechanism to link the index using the id column to the other tables like mailheaders etc.
4) Need an automatic index update mechanism every time a mew mail is inserted into the database.

Any suggestions will be greatly appreciated



Edited 1 time(s). Last edit at 02/03/2010 12:48AM by sylvester dsouza.