mail.appsqliteterminal
Reading up on how to improve my suddenly-very-slow Mail app, I found the tip to rebuild the sqlite index:
sqlite3 ~/Library/Mail/V4/MailData/Envelope\ Index vacuum;
But I get an immediate "Segmentation Fault: 11" and nothing happens. Can't figure out what that means or how to solve it. It's about the first time Google hasn't provided an answer for me. Anyone? (I'm on 10.12.x which is why it's V4.)
I don't believe that it is possible to change the way unread message counts are aggregated. However, you can change your folders so that they are not displayed under your Inbox. That way your Inbox will only show the count for unread message in your Inbox.
On many mail servers, mailboxes are created and stored underneath the Inbox which is what you are seeing.
INBOX
INBOX.Drafts
INBOX.Sent
INBOX.Trash
INBOX.Junk
INBOX.Todo
By changing your account settings in Mail.app, you can specify the IMAP Path Prefix so that Mail will ignore the first part (INBOX) display these folders independently rather than subfolders of the Inbox.
To do this go to Mail -> Preferences -> Account and select the account in the left column. Then select Advanced on the right and you will see a text box to change the IMAP Path Prefix. Typically, you'll want to change this to INBOX, but this will depend on your mail provider (e.g. Google uses [Gmail]) so you may need to check with your provider.
Apple's mail on 10.11 and older will download all the mail that the server tells it about. The macOS 10.12 mail has a setting to optimize mail storage that might store header information for all mails, but not download the body and attachments of older emails.
You can opt in to that from the Apple menu - about this Mac - storage - manage, but it seems to only be offered when you have significant storage burden from Mail and/or specific mail server configurations.
Since that's how the mail client was designed and codes, you will need to go to gmail's web interface and hide some of the folders from IMAP access to prevent mail from ever seeing the messages. Exchange has a setting to determine a time range of mail to sync, but google and IMAP do not have that feature.
Gmail -> Settings -> Labels
Most people I know that use mail like you describe (keep all of it all the time) choose to not show All Mail in IMAP and then just clean their inbox when the volume becomes unwieldy.
Best Answer
‘Segmentation fault: 11’ almost always means the process ran out of RAM whilst trying to perform some operation/calculation. This would corroborate with your slow Mail due to the size of the database you're trying to process (or it's just corrupted in such a way).
It may also be worth checking your free disk space, since the VACUUM command specifically requires plenty of space:
https://sqlite.org/lang_vacuum.html