Deep Firefox SQL optimization

Last Updated on August 10, 2016 by Dave Farquhar

I was looking deeper into Firefox optimization, and I found Adventures in Firefox-places.sqlite. It’s a pretty intense analysis that goes beyond the usual simple, in-browser SQL vacuum that I’ve mentioned in the past. It was written with Mac OS X and Linux in mind, which is fine, but if you run Windows, you might want to do the same thing.

It has two benefits. It speeds up Firefox, and it reduces the amount of disk space your Firefox profile occupies. The two things are related; smaller databases are quicker and easier to navigate than large ones. As for why you should care about the amount of disk space it takes up, well, on an SSD every megabyte counts.

First, you’ll need a copy of sqlite3.exe. For that, visit http://www.sqlite.org/download.html, scroll down to Precompiled Binaries For Windows, and grab a file that looks something like sqlite-shell-win32-x86.zip. There will be a version number in the filename somewhere, which is why I don’t provide a direct link. You want the current version, whatever that happens to be. Download that, extract the file sqlite3.exe from it, and store it somewhere in your system path. Some people consider it bad form to drop stuff like that into c:\windows\system32, but this is one of about three programs I do that with.

Find your profile

First, you need to find the location of your profile, for two reasons. One is to back it up–always a good thing when you’re messing with databases–and secondly, because you’ll need it in the next step. To back it up, just right-clicking on the profile from Windows Explorer and selecting copy, then right-clicking a blank area and selecting paste is sufficient. And you’ll need to note the location of the profile for the next step.

Create a batch file

Now you need a batch file. Copy the following into Notepad and save it someplace handy. I suggest naming it Firefox-cleanup.bat, but use whatever works for you.

set ff=C:\Users\davef\AppData\Roaming\Mozilla\Firefox\Profiles\y8ixjei7.default
::
sqlite3 %ff%\places.sqlite delete from moz_places where hidden=1 and url like 'http%';
sqlite3 %ff%\addons.sqlite VACUUM;
sqlite3 %ff%\chromeappsstore.sqlite VACUUM;
sqlite3 %ff%\content-prefs.sqlite VACUUM;
sqlite3 %ff%\cookies.sqlite VACUUM;
sqlite3 %ff%\downloads.sqlite VACUUM;
sqlite3 %ff%\extensions.sqlite VACUUM;
sqlite3 %ff%\formhistory.sqlite VACUUM;
sqlite3 %ff%\permissions.sqlite VACUUM;
sqlite3 %ff%\places.sqlite VACUUM;
sqlite3 %ff%\search.sqlite VACUUM;
sqlite3 %ff%\signons.sqlite VACUUM;
sqlite3 %ff%\webappsstore.sqlite VACUUM;


Change the path in line 1 (the portion in boldface) to whatever you found in the previous step.

Line 2 deletes hidden rows in the databases, which according to Ryan Schwartz’s analysis (mentioned above) indicates those sites have never been visited. So why is it storing them? Dumping those lines reduced the time required to start Firefox on his system from minutes to seconds. I’ve never had Firefox get that slow. But I don’t want it to happen to me, either.

Run the batch file

Every few months or so, as ongoing maintenance, close Firefox and run the batch file above.

Your results will vary, but on my machines the size of my profile dropped 7-10 megabytes, which is 30 percent. Not too shabby. And several things are faster. Starting Firefox is faster–it takes about 3 seconds on my aged OCZ Vertex SSD now. Pulling up history in the URL bar is faster. Pulling up history in web forms is faster. Processing downloads after the download is finished is faster. And call me crazy, but it seems to render some web pages faster too.

What is crazy is that you’ll probably need to do this less often than Mozilla feels the need to release a new version.