Thursday’s tip about trimming the fat out of Mail’s Envelope Index for a leaner, faster Mail.app got a fair bit of coverage.
Two readers liked it so much that they have produced applescripts to do the job.
Sebastian Morsch 
has written a script that quits Mail, runs the sqlite commands and then relaunches Mail at the end of the process. You can get the script from his web site.
“pmbuko” has written another, which does the same thing (modified as suggested in the comments below by Romulo — Thanks!) :
tell application “Mail” to quit
set sizeBefore to do shell script “ls -lah ~/Library/Mail | grep -E ‘Envelope Index$’ | awk {’print $5′}”
do shell script “/usr/bin/sqlite3 ~/Library/Mail/’Envelope Index’ vacuum”
set sizeAfter to do shell script “ls -lah ~/Library/Mail | grep -E ‘Envelope Index$’ | awk {’print $5′}”
display dialog (”Mail index before: ” & sizeBefore & return & “Mail index after: ” & sizeAfter & return & return & “Enjoy the new speed!”)
tell application “Mail” to activate
It gives you some feedback on what happens:
You can grab the text of the second script above or download it in a zip file I’ve uploaded to the site.
Obviously, the law of diminishing returns is in play here. The first time you run it, the benefit is tremendous. Run it again soon after and there is not so much bloat to remove and, hence, not as great an improvement.
Still, as some have pointed out, this should be a regular maintenance routine for Mail. You can wait for the Mail Development Team to implement it or with one of these rules and iCal, you can automate it yourself.
All you need to do is create a recurring event in iCal and use the “Run script” option in the alarm feature to run the script.
So, save the script somewhere sensible — I saved mine in the ~/Library/Scripts/ Applications/Mail folder.
Create the event.
Click on the alarm field in the event’s information pane, and select “Run script”.
When prompted, navigate to the script and select it.
I’ve added an end for the event after 100 instances because my Palm really hates events with no end date. You may be luckier.
Now you can enjoy a lean Envelope Index long after you have forgotten about the tip.
[With thanks to Sebastian and pmbuko]
UPDATE: If you prefer Automator to AppleScript, Steve Weintraub has created an Automator action to do the same thing.
Neat idea!
However, the script stops for me at this line:
saying
“AppleScript Error. Unable to open database “Envelope Index”: unable to open database file.”
The only option is to click OK.
Any ideas?
TIA
(Repost because the script line got dropped on first attempt)
Neat idea!
However, the script stops for me at this line:
do shell script “/usr/bin/sqlite3 ‘Envelope Index’ vacuum”
saying
“AppleScript Error. Unable to open database “Envelope Indexâ€: unable to open database file.â€
The only option is to click OK.
Any ideas?
TIA
Hmmm… I am not very smart about troubleshooting either applescript or SQL. Both scripts ran fine for me.
Hopefully someone who knows what they are talking about will come by with the right answer. In the meantime, one thing occurs to me…
Could it be that the script is trying to execute the sqlite3 command before Mail has completely quit, so that the database is still “locked”? What happens if you quit Mail first and then run the script?
The script will quit Mail, and then stop. But I also stopped Mail, waited until it completely quit, and retried. The same thing happened, unfortunately.
I’ll wait with you for the script guru, then…. :-)
OK, how about the time-honoured Help Desk option of side-stepping the problem?
What happens if you use the other script?
The problem is that the script is executing “sqlite3″ not in the same directory as ‘Envelope Database’. The solution is to modify the script so that it uses an absolute pathname to find the database. The script should be modified as follows:
tell application “Mail” to quit
set sizeBefore to do shell script “ls -lah ~/Library/Mail | grep -E ‘Envelope Index$’ | awk {’print $5′}”
do shell script “/usr/bin/sqlite3 ~/Library/Mail/’Envelope Index’ vacuum”
set sizeAfter to do shell script “ls -lah ~/Library/Mail | grep -E ‘Envelope Index$’ | awk {’print $5′}”
display dialog (”Mail index before: ” & sizeBefore & return & “Mail index after: ” & sizeAfter & return & return & “Enjoy the new speed!”)
tell application “Mail” to activate
The only line that is different from the previous script is this one:
do shell script “/usr/bin/sqlite3 ~/Library/Mail/’Envelope Index’ vacuum”
In the previous script it was:
do shell script “/usr/bin/sqlite3 ‘Envelope Index’ vacuum”
Hope that helps.
Rom
Aha, excellent!
See, Jon, someone who knows what they are talking about.
Thanks, Romulo :)
I have doubts to execute this trick… ¿does it disable any feature? I mean, if I execute it, will I Mail.app be able to search my contacts in the address book? And one more thing, does it work the same way for Tiger’s “spanish edition”? Are the folder the same name?
Julen, it’s always a good idea — as I wear my fingers out typing for Hawk wings posts — to back up before trying anything like this.
But I have been using it regularly for ages and have not experienced any ill effects at all.
This script will not disable any features. What this script does is reindex your “Envelope Database”. If you don’t know what database reindexing is here is a (slightly modified) explanation:
“What is a database index? A database index is similar to an index for an instruction manual or a book. When you want to locate something in a book it isn’t necessary to read the entire book from cover-to-cover every time you want to find a specific piece of information. Instead, you just look in the index and go to the page that contains the information you’re looking for.
Every time you change and/or add a record (in this case adding/deleting/modifying an email) in your database, the index files are changed. These changes are usually added to the end of the index, instead of being placed in proper order. With time your searches become less efficient since they are now out of order.
For this reason index files need to be put back in order every once in a while. This is called reindexing.”
I took this from another site (cause I’m lazy) which is completely unrelated to mail.app since database reindexing is a general concept.
Note: Even clicking a mailbox is equivalent to a search i.e. you are searching for all mail that belong in that mailbox, which is why you get speed improvements across the board with this technique.
Rom
Thank both Tim and Romulo (and others coming)… I did have no idea what exactly the scripts does unti now…
About the backup… is it fine just copying the files in Home/Library/Mail and the file com.apple.mail.plist (in Home/Library/Preferences) both in a “safe” place? Maybe I would need a third party app?
I know this question is not quite easy but, the script above works fine with the “spanish” names of the folders in OS X?
Thanx (and sorry about my english)!
Julen,
Copying Home/Library/Mail and com.apple.mail.plist should be sufficient. You don’t need a 3rd party applications to do this.
As for spanish folder names, I believe the script will work correctly. Databases don’t understand languages .. whether it is Spanish, English, French etc. To a database “rojo” and “green” are different not because they are different colors but because they have different *characters*. So when the database is reindexing it will just be looking at the characters of the data and reindex accordingly.
Rom
Romulo,
Thanks a lot! That did it. Just modified the one line to include absolute path, and it works like a charm. Beautiful. I _might_ have figured it out – in time – but I probably would have given up first. Kudos!
Jon
I think this bit of Terminal trickery will eliminate the need to run either AppleScript. What should be happening after this is that the db *should* be auto-vacuumed. This could cause a bit of a performance hit but I’ve not noticed anything.
Essentially what I’m doing here is creating a new Envelope Index, setting the auto-vacuum pragma (which has to be done before any tables are added) then dumping the contents of the old index to the new file. Et viola, your index should be auto-vacuumed from now on.
Open up your Terminal and:
cd ~/Library/Mail
mv Envelope\ Index Envelope\ Backup
sqlite3 Envelope\ Index “PRAGMA auto_vacuum=1;”
sqlite3 Envelope\ Backup “.dump” | sqlite3 Envelope\ Index
then, after you’ve started up Mail.app and made sure everything’s working fine:
rm Envelope\ Backup
There’s a warning on the sqlite3 website that I find totally incomprehensible: http://www.sqlite.org/pragma.html#pragma_auto_vacuum
However, I’ve noticed no ill effects.
Nice scripts.
To reiterate (because it came up again). Vacuum results in three changes to the database, two of which speed up queries.
- it eliminates unused pages
- it copies the entire database file, writing the pages of the file in an optimal order
- because it touches every page of the file, it’ll also detect any corruption (which can happen if your mac loses power at exactly the wrong moment)
The first two are the optimization part. In particular, even if you never delete a message, you can still see a bit of a speedup as pages may get out of order as fallout from reading messages and moving them between mailboxes.
http://www.friday.com/bbum/2007/03/02/vacuuming-mails-envelope-index-to-make-mail-faster/
It looks like the folder “Library” is not localized in Spanish OS X:
http://docs.info.apple.com/article.html?artnum=107379
so if you see a folder literally named “Mail” inside of “Library” in your home folder, then yes, this script will work for your OS X.
I’m a POP3 user and go the following results:
Before: 1M After: 556k
Hooray!
Neat trick, and I was all set to do it because Mail does sometimes just seem to drag.
But my Envelope Index files for work and home are already just 468k and 1 MB, respectively. I guess I must be doing something right. And Mail drags its feet from time to time for an entirely different reason.
Why this works: http://www.friday.com/bbum/2007/03/02/vacuuming-mails-envelope-index-to-make-mail-faster/
Chris, as I can look in my computer there is no “Library” folder, but it is “LibrerÃa”, the spanish translation of the first. Although the Apple’s document sayis the opposite I think it is referering to 10.2 version and in 10.4 the folders could be all translated. Anyway I am an POP3 user so maybe this is a big mess for little or no improvement, isn’t it?
Julen,
I now understand what you mean by spanish folder names. I thought you meant spanish mail folder names, not spanish finder folder names (like Libreria).
The easiest way to get the Applescript working is to modify it so that all the english named files/folders are replaced with the appropriate spanish named ones.
I have pasted the same script from above but I have marked the text that needs to be translated into the proper spanish folder names. Look for all text that is capitalized like “LIBRARY” and replace it with “Libreria”. Also replace “MAIL” with the proper spanish name. It is also possible that the file “Envelop Index” may have a spanish name but I’m not sure. You will have to look in your mail folder to see what it is called. If it is not called “Envelop Index” then you will need to change all occurrences of it in the Applescript so that it matches.
Rom
tell application “Mail†to quit
set sizeBefore to do shell script “ls -lah ~/LIBRARY/MAIL | grep -E ‘Envelope Index$’ | awk {’print $5′}â€
do shell script “/usr/bin/sqlite3 ~/LIBRARY/MAIL/’Envelope Index’ vacuumâ€
set sizeAfter to do shell script “ls -lah ~/LIBRARY/MAIL | grep -E ‘Envelope Index$’ | awk {’print $5′}â€
display dialog (â€Mail index before: †& sizeBefore & return & “Mail index after: †& sizeAfter & return & return & “Enjoy the new speed!â€)
tell application “Mail†to activate
Julen,
It should not be necessary to modify the script, I think. The Finder uses localized names, but if you check them in the Terminal, you will see the English standard Unix names. Try it, but I think the English should work.
Jon
Jon,
Oh, I see. I didn’t know it worked like that. Cool.
Rom
great tip. super fast now! thanks
Hey, this is great. Thanks for the script, and I see the zipped version already includes the ~/Library… location in which for vacuum to execute.
I only cleared out 2MB, from 10MB sized envelope to 8MB, but then I run a pretty lean Mail system and 20% ain’t nothing to sneeze at.
Hello,
When I use the applescript script I do get time out’s. Does that mean that my envelop file is to big? I can see that its working but it never gets finished.
I double-clicked on Mail Vacuum and it opened Automator which then just sat there and looked at me. No effect on envelope index whatever.
I’m a complete novice at this stuff so maybe I did something wrong.
In case anyone’s wondering, I have an ibook g4 running 10.4.8.
I deleted a bunch of old mail yesterday, and ran one of the Envelope tricks again this morning, and went from 15MB to 7MB. Not bad at all!
Copying and pasting the script into Script Editor doesn’t work, due to smart-quotes. Downloading script instead, thanks!
@William Taylor:
Did you click the “Play” button in Automator?
This script pretty much saved mail.app for me. I almost completely switched to gmail because searching for mail with gmail was always faster. With such an apparent improvement in search I can’t believe this isn’t a standard routine implemented by Apple.
This and mail tags makes mail.app completely useful. Thank you.
Speeding up Mail.app
I copy/pasted the Unix commands into Terminal. After starting Mail, It told me that it had to import all my mail boxes…5hours!!!
Is this right?
Tony
Unfortunately neither script seems to work in 10.3.9, nor does posting various suggested terminal commands from the comments on the previous post.
Does anyone have a script for doing this in 10.3.9?
thanks
Pete, this doesn’t work in Panther, because Panther Mail doesn’t have — or need — an Envelope Index file.
The database was introduced with Tiger as a way of storing information about all the individual emlx files that were also introduced with Tiger in place of the “old” single-file mbox storage for mailboxes.
thanks Tim, too bad, is there any way to speed up mail in panther without upgrading to 10.4?
ie searching or sorting is often painfully slow and i’ve got lots of ram
Can you last until June (or July or…?) when Leopard comes out?
Before: 18M
After: 9M
Wow!
When I first copied the script, I did not remove the “curly quotes” and got all sorts of script errors. The downloaded zip file works perfectly!
Intel iMac 2GB RAM ~1TB HD.
Thanks, earle
Correction: The pmbuko script shrunk my index from 106MB to 101MB.
I used the script from the zip-file and got the “database is locked”-error on my first try. So I kept reading to find a solution, but got nothing. I then tried again and it worked. Only change: Mail was already closed. No changes in size (3M / 3M), but it seems to react faster now. Thanks anyway!
Hey, this script is really pretty cool.
But before I will use it regularly: Is there a need to use this under Leopard further? Or does Mail in the newest version under Leopard makes something like this on its own?
greets
Florian
[...] For more details see this blog post or hawk wings. [...]
[...] at Hawk Wings, they’ve posted a neat AppleScript to help optimize Mail.app I ran the command line version [...]
[...] to automate the happy vacuuming process — a jab I didn’t take kindly to — I found a post by regeya. Essentially what I’m doing here is creating a new Envelope Index, setting the auto-vacuum pragma [...]
hi . i would like to know if you also have script that automatically extract attachment from mail.app to my drive . tnx