1487532 - Highlights queries for Activity Stream home panel are slow

Status: RESOLVED FIXED

(Firefox for iOS :: Data Storage, enhancement, P2)

Description

[Justin D'Arcangelo [:justindarc]]

Every time the Activity Stream home panel is opened, the following two queries run:

getHighlights() - 0.258s
------------------------
SELECT historyID,highlights.cache_key AS cache_key,url,highlights.title AS title,guid,visitCount,visitDate,is_bookmarked,iconID,iconURL,iconType,iconDate,iconWidth,page_metadata.title AS metadata_title,media_url,type,description,provider_name FROM highlights LEFT JOIN (SELECT * FROM view_history_id_favicon WHERE id IN (SELECT historyID FROM highlights)) AS f1 ON     f1.id = historyID LEFT OUTER JOIN page_metadata ON     page_metadata.cache_key = highlights.cache_key

getBookmarksForHighlights() - 0.304s
------------------------------------
SELECT historyID, url, siteTitle AS title, guid, is_bookmarked, iconID, iconURL, iconType, iconDate, iconWidth, page_metadata.title AS metadata_title, media_url, type, description, provider_name FROM (SELECT historyID, url, siteTitle, guid, is_bookmarked FROM (     SELECT history.id AS historyID, history.url AS url, history.title AS siteTitle, guid, history.domain_id, NULL AS visitDate, 1 AS is_bookmarked     FROM (SELECT bmkUri FROM bookmarksBuffer WHERE server_modified > 1535228783585 AND is_deleted = 0 UNION ALL SELECT bmkUri FROM bookmarksLocal WHERE local_modified > 1535228783585 AND is_deleted = 0)         LEFT JOIN history ON history.url = bmkUri         INNER JOIN (SELECT view_history_visits.domain_id AS domain_id FROM view_history_visits GROUP BY view_history_visits.domain_id) AS domains ON domains.domain_id = history.domain_id     WHERE         history.title NOT NULL AND         history.title != '' AND         url NOT IN (SELECT activity_stream_blocklist.url FROM activity_stream_blocklist)     LIMIT 2 ))     LEFT JOIN view_history_id_favicon ON         view_history_id_favicon.id = historyID     LEFT OUTER JOIN page_metadata ON         page_metadata.cache_key = url GROUP BY url

These time measurements are NOT on-device. These measurements were from running `sqlite3` on my laptop with `.timer on` on a copy of browser.db with ~200k rows of history. Even still, the combined query time is ~500ms on an i9 MacBook Pro.

Comment 1

[:garvan]

These are 1.6s on an iPhone 5c, on my personal Fx account. I think that is 50K rows, I can confirm if needed.

Comment 2

[Justin D'Arcangelo [:justindarc]]

Landed on master:

https://github.com/mozilla-mobile/firefox-ios/commit/46d8bccd2640cf7b9c66efbc8643b22b0c500621