11

Context:

I've been using my Firefox profile for a couple of years now. My history file has become huge, naturally. I got Firefox Sync set up between my main desktop PC and my laptop.

HW configs:

  • PC: i5-3450, 8 GB DDR3 RAM, Crucial M4 128 GB SSD
  • laptop: Pentium SU4100, 4 GB DDR3 RAM, WD 5400 rpm HDD

Accessing history entries when typing into the Awesome Bar on my desktop takes quite a long time despite the decent config, the laptop is even slower. The experience is quite unresponsive.

I figured if I cleared the history up a little bit, I might avoid creating a new profile to speed things up.

The question itself:

To illustrate:

history

Is there a way to delete all history entries that have been visited fewer than x (let's say 5) times and at the same time the recent visit is fewer than y (let's say 120) days old?

afaik the history file is some kind of SQL database, but I'm not really sure how the data is saved, if there's a "safe way" to edit it and what the query to do what I need would look like.

I kept browsing through previous SuperUser questions to see if I could find relevant information.

In my Firefox profile directory, there is a file named places.sqlite. Opening it with sqlite reveals (among others) the tables moz_places and moz_historyvisits. It seems that moz_historyvisits uses the primary of moz_places to refer to the URLs.

As I'm unfamiliar with databases, I don't really understand the way the two tables mentioned in the quote are related.

screenshot of a part of the tables places_sqlite

I've noticed the visit_count is in a standard format, making it easy to work with. The last_visit_date looks encrypted to my naked eye, but I can't see in which way.

Hope that helps, I'm at my wits' end.

Improve this question
  • To answer one of your questions, according this answer, Firefox does in fact use SQL. I recommend firing up SQLLite and go from there. – AStopher May 29 '14 at 16:04
  • What did you try so far? Before you proceed, can you confirm that a brand new Firefox profile does provide a snappier experience, especially on the laptop? – and31415 May 29 '14 at 16:36
  • 1
    I've just posted an answer that contains some new information. As for the snappiness, no, I haven't tried, but I distinctly remember the Awesome Bar was much snappier when the profile was quite new (and the history uncluttered by some many entries), both on the laptop and the desktop. – lousyuser May 29 '14 at 17:21
  • So did you see any performance improvement after clearing your history? -- edit: oops: I see now that you've commented on the selected answer and you did noticed great improvement. – ndemou Dec 22 '15 at 15:58
9

With some research (see at end for 'references'), I've come up with this (kind of) simple solution:

BACKUP FIRST. CLOSE FIREFOX FIRST

This requires you to edit the SQLite Database (a self-contained SQL database; see Wikipedia article) of Mozilla Firefox by making a query to find all records that satisfy the condition (in your case - viewed less than 5 times and not visited in the last 120 days).

BACKUP FIRST. CLOSE FIREFOX FIRST

Instructions start:

This works (tested and working with my Firefox v. 29.0.1). In 12 easy(ish) steps:

  1. Install SQLite Database Browser

  2. Open it

  3. Click Open Database

  4. Navigate to C:\Users\[USER]\AppData\Roaming\Mozilla\Firefox\Profiles\[PROFILE] editing as required

    BACKUP THE FOLLOWING FILE FIRST!!

  5. Open places.sqlite from the directory you opened in step 4.

    SERIOUSLY, BACKUP FIRST!!

  6. Click the Execute SQL tab.

  7. Enter this:

    SELECT *
FROM moz_places
WHERE
    last_visit_date BETWEEN strftime('%s','2014-04-01')*1000000
                    AND strftime('%s','2014-05-30')*1000000
    AND visit_count < x(2)

  8. Replace x(2) to 5 (in your example)

  9. Replace 2014-04-01 and 2014-05-30 with the range of dates you want

  10. Press the blue play button.

  11. Check if the sites displayed are correct (they should be, but double check!)

  12. If they are, then replace the first two lines of the above code with:

    DELETE
FROM moz_places

    so your code looks something like:

    DELETE
FROM moz_places
WHERE
    last_visit_date BETWEEN strftime('%s','2014-04-01')*1000000
                    AND strftime('%s','2014-05-30')*1000000
    AND visit_count < x(2)

DONE! Close the program and click save when closing.

Instructions end

What about the last_visit_date?

The last_visit_date looks encrypted to my naked eye, but I can't see in which way.

I wouldn't call it 'encrypted' (although I don't disagree that it looks encrypted). It's just in another 'system'. The date is in the Unix time system (or Epoch / POSIX). It's the number of seconds that have passed since 00:00:00 (UTC) 1st January 1970. 1 hour is 3600 seconds. 1 year is 31556926 days.

For more info. check this Wikipedia article out, or this website that converts time to and from epoch time.

Screenshots (ignore the SQL queries on these images, they are wrong. These are to tell you mostly where to be looking)

SQLite Database Browser SQLite Database Browser SQLite Database Browser

Thanks to ;):

Improve this answer
  • For added security, you might want to make a copy (backup) of the database before doing this. – Bob May 29 '14 at 17:57
  • Also, if you want to do this properly, you should also delete from moz_places any entries that no longer have a corresponding place_id. – Bob May 29 '14 at 17:59
  • Sorry, I meant delete from moz_historyvisits – Bob May 29 '14 at 18:22
  • @Bob And place_id in moz_historyvisits = id in moz_places? – ᔕᖺᘎᕊ May 29 '14 at 18:23
  • Should be. There are two ways to handle it: 1. delete from moz_historyvists first (delete from moz_historyvisits as hv left join moz_places as p on hv.place_id = p.id where p.visit_count...) or 2. after clearing moz_places, you can delete from moz_historyvisits any entries that don't have the corresponding entry. I prefer option 1 personally. Don't use it without testing; my SQL is rather rusty, and I learned with pgsql, not sqlite. You might actually be able to delete from both at once, lemme test. – Bob May 29 '14 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.