Firefox 29 – how to delete history entries visited fewer than x times

firefoxfirefox-profilehistoryplaces.sqlite

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.

Best Answer

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 ;):

Related Question