Every guide I found for scripting Apple Mail reaches for the same tool: AppleScript’s whose filter, something like messages of mailbox whose subject contains "...". That’s fine on a small mailbox. On a mailbox with 150k+ messages it routinely takes one to three minutes, sometimes it just times out, and in the worst case it can wedge Mail’s entire automation bridge, a stuck AppleEvent that blocks unrelated automation calls until it resolves or times out on its own.
The instinct is to look for a smarter AppleScript query. There isn’t one. No property makes whose fast, not subject, not sender, not even Mail’s own internal id. whose is a linear scan over Apple’s Event bridge, one round trip per message, no matter what you’re filtering on. The actual fix is to stop asking Mail.app anything for the search step, and go straight to the database Mail already built for itself.
Mail already has an index, just ask it directly
macOS Mail keeps its own search index as a plain SQLite database, at ~/Library/Mail/V*/MailData/Envelope Index (the V* folder number depends on your macOS/Mail version). It’s the exact data Mail uses for its own instant in-app search, subject, sender, dates, mailbox, read and flag state, all properly indexed. Open it read-only:
sqlite3 -readonly ~/Library/Mail/V10/MailData/"Envelope Index" ".tables"Always open it -readonly (or a mode=ro URI if you’re connecting from a script). Mail keeps this file open in WAL mode while it’s running, and it’s an internal Apple format with no schema stability guarantee across versions. Treat it as read-only and best-effort, never write to it.
A search that takes one to three minutes through whose, subject or sender, comes back in single-digit milliseconds through this database instead. Same data, same mailbox, the only thing that changed is who’s doing the lookup.
The schema, and one thing worth getting right up front
It’s a plain relational schema, not Core Data. I mention that specifically because a few existing writeups floating around online describe a ZMESSAGE/Z_PK/ZSUBJECT Core Data-style schema instead, which doesn’t match what’s actually on disk. Copy one of those queries and you get “no such table,” not a search result.
The real shape, checked directly against the schema on disk:
messages: primary key is a plainROWID, and that’s the same integer AppleScript exposes as a message’sidproperty, cross-referenced and confirmed.subjectandsenderare foreign keys intosubjectsandaddressestables, not inline text, Mail dedupes repeated subject lines rather than storing the string on every row. Plusdate_received,mailbox, andread/deletedflags.recipients: one row per message-recipient pair, with atypecolumn,0for To,1for Cc. Bcc is never stored locally at all, for privacy and protocol reasons, there’s simply nothing to find.
A message’s subject and sender by id, joining the two lookup tables:
SELECT s.subject, a.address -- the subject text and the sender's address
FROM messages m
JOIN subjects s ON m.subject = s.ROWID -- subject is a foreign key, not inline text
JOIN addresses a ON m.sender = a.ROWID -- sender works the same way
WHERE m.ROWID = 12345; -- the message's internal id, same integer AppleScript's `id` usesThe fun part: resolving a real Message-ID entirely offline
Every email carries a Message-ID: header, something like <abc123@mail.gmail.com>, and it’s worth being clear on why that header exists at all before doing anything clever with it. It’s the one identifier every mail system actually agrees on. A message’s Message-ID gets generated once, by whatever server or client sends it, and travels with the message unchanged across every server, client, and account it passes through afterward, unlike Mail’s own internal numeric id, which only means anything inside that one local database. That’s what makes it useful for two real things: threading, a reply’s In-Reply-To and References headers point back at the Message-ID of whatever it’s answering, which is how a client reconstructs a conversation even across different accounts, and deduplication, the same physical message can land in more than one mailbox, and the Message-ID is how anything recognizes it as the same email rather than two different ones.
That’s also exactly the gap this reverse-engineering closes. A script automating Mail.app often already has a message’s Message-ID for free, sitting right in another email’s In-Reply-To/References headers, or handed over by whatever produced the metadata in the first place. Mail’s own local database doesn’t index on that string at all though, it only keeps a private hash of it for its own internal dedup. So there was no way to go from an id already in hand to the actual row in Mail’s database, short of a slow full-mailbox scan or asking Mail.app directly. Reverse-engineering that hash closes exactly that gap: it turns an id you already have into a plain database lookup.
Mail hashes the bare Message-ID string into a messages.message_id column, a signed 64-bit integer, indexed, that it uses for its own deduplication internally. Checked against nine independent known Message-IDs, different formats, different senders, sent and received mail, zero mismatches:
import hashlib, ctypes
def mail_message_id_hash(msgid: str) -> int:
msgid = msgid.strip().strip("<>") # "<abc123@mail.gmail.com>" -> "abc123@mail.gmail.com"
digest = hashlib.md5(msgid.encode()).digest()[:8] # MD5 the bare string, keep only the first 8 bytes
return ctypes.c_int64(int.from_bytes(digest, "little")).value # those 8 bytes as a little-endian signed 64-bit int, same storage shape as the SQLite columnTake the MD5 hash of the bare Message-ID (no angle brackets), keep the first 8 bytes, read them little-endian, interpret the result as a signed 64-bit integer. Compute that once, and the lookup is a plain indexed equality check:
SELECT ROWID FROM messages WHERE message_id = ?;If a script or agent already has a Message-ID in hand, pulled from an email’s own headers, or from an In-Reply-To/References header on some other message, it can confirm the message exists, which mailbox it’s sitting in, and who it went to, entirely offline, in milliseconds, without asking Mail.app anything at all.
One honest caveat: this lookup can legitimately return more than one row for the same Message-ID. Not a hash collision, genuine duplication, an account configured to receive the same message through more than one delivery path (natively via one account, relayed into another) really does end up with two separate rows for what is, to the sender, one message.
What this trick can’t do
It’s read-only and disconnected from Mail’s live process, so it’s built for searching and verifying, not for acting. It can’t make Mail open, reply to, or forward anything, for that you’re back to asking Mail.app itself. The fast way to do that specific part is Mail’s own message:// URL scheme:
osascript -e 'open location "message://%3Cabc123@mail.gmail.com%3E"'URL-encode the angle brackets, < becomes %3C, > becomes %3E. This makes Mail jump straight to that exact message through its own internal Message-ID index, a genuinely different, fast code path from whose-based search, typically around ten seconds even against a huge mailbox, against one to three minutes or worse for the AppleScript filter. Add -g to open if you don’t want it stealing focus from whatever you’re actually working in.
Three tools, three jobs
Search or verify by subject or sender, query the SQLite index directly, milliseconds. Verify one exact, already-known Message-ID, hash it and look it up in the same database, milliseconds, no ambiguity. Actually act on a message, open/reply/forward, message:// through AppleScript, about ten seconds, and the only step that has to touch Mail’s live process at all.
Don’t reach for whose-based AppleScript search for any of the first two. It’s the slowest option on the table, and the one capable of wedging Mail’s own automation bridge for everyone else waiting on it.
Leave a Reply