History sidebar supports multiple views, for each view there is one query performed which reads all the data to be displayed on the view. The more data is in the database the longer it takes to display the sidebar.
- 1 Testing conditions
- 2 Views
- 2.1 View by Date and Site
- 2.2 View by Site
- 2.3 View by Date
- 2.4 View by Most Visited
- 2.5 View by Last Visited
- 3 Searching
- 4 Incremental loading
- No favicons (they seems to slow down display of the sidebar and should be solved separately).
- Loading time is just the time spent in the database engine.
- Time is always in ms unless specified else.
- Three databases of various size:
View by Date and Site
Loads all places and their visits, performs grouping by date and site in memory.
This just combines "View by Date" and "View by Site" - there will be on demand loading on two leves, first by the date and then by the site. Only last visits to each place in given site and time range will be displayed. This will be the fastest view.
View by Site
Loads all places and their visits, performs grouping by site in memory.
Future SituationOnly list of sites will be loaded initially.
SELECT DISTINCT rev_host FROM moz_places WHERE hidden <> 1 ORDER BY 1 DESC;
Even for big database, the time seems to be acceptable. Pages inside the sites will be loaded on demand using the rev_host as filter, we will be selecting only latest visits to each place on the given site.
View by Date
Loads all places and all related visits to memory, processes the data in memory and creates groups by days. This takes with big database more then 30 seconds. When user clicks on the day, more processing is done, with big database and "Older than 6 days" range this can take 3 minutes. Collapsing and expanding this range takes the same time.
Future SituationThere will be 8 queries performed which will check for which ranges we have visits (Today, Yesterday, 2 days ago, ...). No visits will be loaded, just the ranges will be presented. A query to check whether there is a visit for the given date:
SELECT visit_date FROM moz_historyvisits WHERE visit_date BETWEEN CAST(strftime("%s",current_timestamp, "-1 days") AS UNSIGNED)*1000000 AND CAST(strftime("%s",current_timestamp) AS UNSIGNED)*1000000 LIMIT 1;This query takes about 0.5ms on any size database, together these 8 queries take 4ms. So the sidebar would be displayed immediately. When the user clicks on any range, only places visited in the period will be loaded and sorted by most recent visits:
SELECT h.id, h.url, h.title, h.rev_host, h.visit_count, MAX(v.visit_date), f.url, v.session, null FROM moz_places h JOIN moz_historyvisits v ON h.id = v.place_id LEFT OUTER JOIN moz_favicons f ON h.favicon_id = f.id WHERE visit_date BETWEEN CAST(strftime("%s",current_timestamp, "-1 days") AS UNSIGNED)*1000000 AND CAST(strftime("%s",current_timestamp) AS UNSIGNED)*1000000 AND h.hidden <> 1 AND v.visit_type NOT IN (0,4) GROUP BY h.id ORDER BY MAX(v.visit_date) DESC;
|Database||Today||Yesterday||Older than 6 days|
This gives reasonable times with exception of the "Older than 6 days", which would be slow even for mid database. It requires incremental loading.
View by Most Visited
Joins places with history, this could be avoided if the visit_count is only updated for proper visit_types (to be checked).
Requires incremental loading.
View by Last Visited
Correct query, loads all the places and their last visit.
Requires incremental loading.
Searching loads all places from the database and performs searching on UI. This is done for each group of letters typed.
We should repeat the query only when user removes some characters from the search, not when he types more (in such case, we should do filtering in memory). We should be searching using LIKE for title and url. This leads to full scan on the table, but this full scan is done anyway, so filtering on backend should speed the searching up.
Even with possible on demand loading, there are still some queries, that would be too slow. Fortunately all such views can be loaded in chunks using existing indexes. We should not load all the data in the sidebar immediately, but instead we should load the data as the user scrolls down.