1864900 - history: Thoughts on reducing on-disk size

Status: NEW

(Application Services :: Places, defect, P3)

Description

[Mark Hammond [:markh] [:mhammond]]

From github: https://github.com/mozilla/application-services/issues/639.

This is the size breakdown of my places database imported from desktop using the autocomplete import script:

https://gist.github.com/thomcc/243776a4235eaf74c5a1af5d8eee2235

This is after VACUUMing. It's 70MBish and has this many records:

sqlite> select count(*) from moz_places;
162689
sqlite> select count(*) from moz_historyvisits;
253521
sqlite> select count(*) from moz_origins;
10358

It's applied on top of #632, because that's the branch I'm in. I don't expect that to make much of a difference.

It's worth noting that we have an ISLOCALINDEX which is almost entirely useless (See number 3 here: https://www.sqlite.org/queryplanner-ng.html#_checklist_for_avoiding_or_fixing_query_planner_problems -- "In particular, avoid using boolean or "enum" columns as the left-most columns of your indexes"). I think thisis my fault, I added the index before reading that document.

It's also possible that LASTVISITDATELOCALINDEX and LASTVISITDATEREMOTEINDEX, as well as VISITCOUNTLOCAL/VISITCOUNTREMOTE can be unified. A lot of our queries look like visit_count_local > 0 OR visit_count_remote > 0. We could instead add an expression index on visit_count_local + visit_count_remote and have a single (visit_count_local + visit_count_remote) > 0. The same might be possible with last_visit_date_local/remote.

┆Issue is synchronized with this Jira Task
┆Epic: Important backlog

Change performed by the Move to Bugzilla add-on.