[Dovecot] Using MySQL to store email?

[Curtis Maloney]

Marc Perkel wrote:
> 
> Suppose you have a total of one million messages stored for 5000 users 
> across 800 domains and you want to delete all message that were sent 
> from a specific host. With MySQL it's a one line command and would take 
> only a few seconds to execute. Using Maildir it would take hours because 
> you would have to search every message. That's the power of MySQL.
> 

And really, her we come to the major advantage of using an SQL driven database: 
Ad-hoc operations.

I recall a tale my father ( a veteran of 30 years in IBM ) told me of when IBM 
started rolling out SQL databases.  The clients used to wail loudly about how 
_slow_ they ran.  IBM said they'd pull them out, and go back to the old ways.

The clients wouldn't let them.  The ability to do arbitrary and ad-hoc queries, 
and to build customised analyses on the fly, far outweighed the performance hit.

And this seems to be the crux of your argument.  With an SQL database, you can 
create new and exciting queries on your data easily.  However, expect to take a 
hit on the common case operations.

> Maildir only is fast for indexing file names. But if you are indexing 
> across users and domains by host, or headers, or senders, or whatever 
> then only a database can support these multiple indexes. There are 
> things you can do with databases that are way beyond what you can 
> imagine. Especially if you are integrating it with a spam system.

Please stop using the word "database" as if it means "SQL DBMS".  It's as bad as 
those marketing twits using the word "broadband" to mean "high data rate".

And again here we see my point.  When you want to do the common case, the 
existing, job specific databases (Maildir and mbox) are good at it.  Why? 
Because they were designed to be.

But when you want to do something else, they're not.  Why?  It was the trade off 
made to make the common case fast and easy.  I don't spend a lot of time 
preparing my home for if the Queen comes to visit.... it's not what I'd call a 
common case, and my time would be better spent elsewhere.

> This issue isn't performance, it's power. If you compare it with what 
> people are doing now then MySQL would probably be a little slower. But 
> if you compare it to what is possible that you can't do now then MySQL 
> wins.

In fact, for some of the stuff I do at work, I'm planning to write a tool that 
will munge some of our job specific databases into SQLite, so we can more easily 
to arbitrary analyses on the data when we need to.  But it'd be a waste to put 
it all in a SQL DBMS from the start.

--
Curtis Maloney
cmaloney at cardgate.net