MediaFrenzy

March 21, 2007

Mail.app internals

Filed under: Cult of Mac,Technology — Allan @ 10:45 pm

I read a cool post the other day that explained how to speed up Mail.app, OS X’s built in mail client.

The secret lies in the fact that Mail.app stores all of your mail in an SQLite3 database, and by running a vacuum command on the subjects table, it’ll speed up mail quite significantly. Never one to shy away from potentially devastating results in a terminal, I gave it a whirl.

My Envelope Index went from 800K to 200K in one swift command. Nice work. And mail loaded, and all my mail was still there. Even better.

Why this works is because when you delete a record from an SQLite database it leaves the empty space that the deleted record was taking up. This makes later inserts into the database faster, but the database remains the same size as it was before you deleted the record. The vacuum command consolidates your database file on your disk (much like defrag does in Windows) and makes the file only as large as it needs to be. The article suggests using the command on the subjects table, but SQLite3 now ignores the table name and vacuum’s the whole database.

While I was there I thought I’d have a poke about and for those that enjoy this sort of thing, the innards of how Mail.app stores and manages your mail:


CREATE TABLE addresses (ROWID INTEGER PRIMARY KEY, address, comment, UNIQUE(address, comment));
CREATE TABLE attachments (ROWID INTEGER PRIMARY KEY, message_id INTEGER, name, type, UNIQUE(message_id, name));
CREATE TABLE mailboxes (ROWID INTEGER PRIMARY KEY, url UNIQUE);
CREATE TABLE messages (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, message_id, in_reply_to, remote_id INTEGER, sender INTEGER, subject_prefix, subject INTEGER, date_sent INTEGER, date_received INTEGER, date_last_viewed INTEGER, mailbox INTEGER, remote_mailbox INTEGER, original_mailbox INTEGER, flags INTEGER, read, flagged, size INTEGER, color, encoding, pad);
CREATE TABLE properties (ROWID INTEGER PRIMARY KEY, key, value, UNIQUE (key));
CREATE TABLE recipients (ROWID INTEGER PRIMARY KEY, message_id INTEGER, type, address_id INTEGER);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE subjects (ROWID INTEGER PRIMARY KEY, subject);
CREATE TABLE threads (ROWID INTEGER PRIMARY KEY, message_id INTEGER, reference, is_originator);
CREATE INDEX address_address_index ON addresses(address);
CREATE INDEX attachments_type_index ON attachments(type);
CREATE INDEX date_index ON messages(date_received);
CREATE INDEX date_last_viewed_index ON messages(date_last_viewed);
CREATE INDEX message_flagged_index ON messages(flagged);
CREATE INDEX message_mailbox_index ON messages(mailbox, date_received);
CREATE INDEX message_message_id_index ON messages(message_id);
CREATE INDEX message_read_index ON messages(read);
CREATE INDEX message_remote_mailbox_index ON messages(remote_mailbox, remote_id);
CREATE INDEX message_sender_index ON messages(sender);
CREATE INDEX recipients_address_index ON recipients(address_id);
CREATE INDEX recipients_message_id_index ON recipients(message_id);
CREATE INDEX references_message_id_index ON threads(message_id);
CREATE INDEX references_reference_index ON threads(reference);
CREATE INDEX subject_index ON messages(subject);
CREATE INDEX subject_subject_index ON subjects(subject);
CREATE TRIGGER after_delete_message AFTER DELETE ON messages BEGIN DELETE FROM threads WHERE threads.message_id == OLD.ROWID; DELETE FROM attachments WHERE attachments.message_id == OLD.ROWID; DELETE FROM recipients WHERE recipients.message_id == OLD.ROWID; DELETE FROM subjects WHERE ROWID = OLD.subject AND (SELECT COUNT() FROM messages WHERE subject = OLD.subject) = 0; DELETE FROM addresses WHERE ROWID = OLD.sender AND (SELECT COUNT() FROM messages WHERE sender = OLD.sender) + (SELECT COUNT() FROM recipients WHERE address_id = OLD.sender) = 0; END;

Incidentially, to get into the sqlite3 command line tool, fire up a Terminal and type:
sqlite3 ~/Library/Mail/Envelope\ Index

.q will get you out of there once you’re done busting up your mail.

SQLite has a pragma command that allows you to tell it to always vacuum the database when a record is deleted. Maybe I should back up my mail before trying that one ….

Advertisements

4 Comments »

  1. aAqqb8 http://gdjI3b7VaWpU1m0dGpvjRrcu9Fk.com

    Comment by frenky — May 8, 2011 @ 9:05 am | Reply

  2. Which year are you in? nymphet gallerys >:-P

    Comment by Lmuqgsfo — September 3, 2011 @ 2:50 am | Reply

  3. I never went to university preteen nn models
    lrtlh

    Comment by Rzyqsjkz — September 7, 2011 @ 6:04 am | Reply

  4. I’d like to cancel this standing order nn teen
    =[[

    Comment by Rpwydbqs — September 8, 2011 @ 8:30 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: