The frecency query [1] we're making for our search recommendations/top sites is painfully slow. The query can up to a few seconds before completing. This is noticeable in places such as Top Sites where we run this query every time the user visits the panel. The items in Top Sites don’t render until the query completes. When taking a closer look at the query we're running to determine frecency, there are a number of factors which are causing it to be slow. Unlike desktop, frecency on mobile is calculated in real time instead of being pre-calculated. This results in a complex query that touches various tables with inner joins and computationally expensive ORDER/COALESCE operations. The query is then run on a potentially large data set since it touches the history table. In cases where a user has attached their FxA account, their desktop history will also be part of this.
After some discussion, there are 2 approaches we can take to optimize the way we handle frecency:
- Tweak/modify the existing query. Th
