Firefox 29 - how do I delete history entries visited fewer than x times

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:

Install SQLite Database Browser
Open it
Click Open Database
Navigate to C:\Users\[USER]\AppData\Roaming\Mozilla\Firefox\Profiles\[PROFILE] editing as required

BACKUP THE FOLLOWING FILE FIRST!!
Open places.sqlite from the directory you opened in step 4.

SERIOUSLY, BACKUP FIRST!!
Click the Execute SQL tab.

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)

Replace x(2) to 5 (in your example)
Replace 2014-04-01 and 2014-05-30 with the range of dates you want
Press the blue play button.
Check if the sites displayed are correct (they should be, but double check!)

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

Thanks to ;):

How to write an SQL query for SQLite Manager for Firefox places.sqlite?
need help pulling up history on specific date (Firefox support forum)
SQL DELETE Statement (w3schools)
How do I decode the Last_Date_ Visited field in the places.sqlite db? (Firefox support forum)

ᔕᖺᘎᕊ

Posted 2014-05-29T15:58:17.697

Reputation: 5 559

For added security, you might want to make a copy (backup) of the database before doing this. – Bob – 2014-05-29T17:57:11.590

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 – 2014-05-29T17:59:15.360

Sorry, I meant delete from moz_historyvisits – Bob – 2014-05-29T18:22:27.240

@Bob And place_id in moz_historyvisits = id in moz_places? – ᔕᖺᘎᕊ – 2014-05-29T18:23:47.813

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 – 2014-05-29T18:26:58.073

Actually, looking at the entity relationship diagram, there's quite a few more places that reference moz_places. It's easier to just assume that Firefox will notice those no longer exist and clean them up accordingly.

– Bob – 2014-05-29T18:37:51.127

@Bob That's what I was thinking. Firefox should at least do that, I just don't know how to check... I can see in the History Viewer that there is no more history left (that I can see) – ᔕᖺᘎᕊ – 2014-05-29T18:55:24.037

Thanks a ton! As for deleting all the invalid entries dependent on the now deleted history records, that'd probably take an extensive knowledge of how Firefox works. However, this will do. The Awesome bar is much snappier now. – lousyuser – 2014-05-29T18:56:50.353

I'll add that you should do these steps only after closing the browser. – and31415 – 2014-05-30T14:57:15.833

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 – 2015-12-22T15:58:00.780

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 – 2014-05-29T16:04:33.460

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 – 2014-05-29T16:36:31.377

1I'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 – 2014-05-29T17:21:54.887

Firefox 29 - how do I delete history entries visited fewer than x times

Context:

The question itself:

Answers