highlights.sql

SELECT *, metadataDB.page_metadata.title AS metadata_title
FROM
(

	SELECT * , id as historyID, max(length(url)), 
		(SELECT count(1) FROM visits WHERE visits.siteID = history.id) as visitCount 
	FROM history WHERE id IN (
		SELECT DISTINCT  siteID AS historyID
		FROM visits WHERE date > 1491243277100158
		AND NOT EXISTS (SELECT * FROM visits WHERE siteID = historyID AND date > 1493055877100158)
	)
	AND domain_id NOT IN (SELECT cached_top_sites.domain_id FROM cached_top_sites)
	AND title NOT NULL AND title != ''
	AND domain_id NOT IN (SELECT domains.id FROM domains WHERE domains.domain IN (?,?))
	group by domain_id
	order by visitCount
	limit 100
)

LEFT JOIN view_history_id_favicon ON view_history_id_favicon.id = historyID 
LEFT OUTER JOIN metadataDB.page_metadata ON metadataDB.page_metadata.site_url = url
ORDER BY COALESCE(iconURL, media_url) NOT NULL DESC /* Favour sites with some metadata */
LIMIT 8 

I'm actually suggesting doing the opposite of what desktop does. Desktop grabs everything into memory and does the work there. We aren't well-placed to do that — the query is more expensive for us, and we have less memory.

But yes, figure out what you can do that's better than the current query, and measure against large real-world data sets. If it's quicker and has lower peak memory use, and the query is clearer, there's no reason not to ship it.