Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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 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 at 17:21

2 Answers 2

up vote 6 down vote accepted

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

share|improve this answer
    
For added security, you might want to make a copy (backup) of the database before doing this. –  Bob May 29 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 at 17:59
    
Sorry, I meant delete from moz_historyvisits –  Bob May 29 at 18:22
    
@Bob And place_id in moz_historyvisits = id in moz_places? –  ṧнʊß May 29 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 at 18:26

To answer one of your questions, according this answer, Firefox does in fact use SQL.

I recommend firing up SQLLite and go from there.

share|improve this answer
    
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. –  Raystafarian May 30 at 15:37
1  
@Raystafarian I answered one of the questions they were asking. That was good enough. –  cybermonkey May 30 at 16:27
1  
Sure it may answer part of the question, but it doesn't cite any documentation, or link to the particular program you're recommending or explain how to solve the problem. It's an incomplete answer as it stands. I'd recommend editing the answer to include some more documentation, details or instructions. –  Raystafarian Jun 2 at 12:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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