Investigate why favicon support can make querying history slow

[tupton]

I have a feeling it has to do with the ATTACH and complex query.

This manifests as a ~1s delay in results, but not on my system.

At minimum, the band-aid is to make favicon optional. It'd be nice to know what the actual slowness is – copying the favicon db? ATTACH? the query itself? – and fix that instead.

[tupton]

I'm pretty sure it's not the copying that's the problem; when the problem manifests it does it even when the db files should be cached. Perhaps look into whether that caching is actually happening?

[jberkel]

Here's the query plan:

sqlite> explain query plan SELECT urls.id, urls.title, urls.url, favicon_bitmaps.image_data, favicon_bitmaps.last_updated FROM urls LEFT OUTER JOIN icon_mapping ON icon_mapping.page_url = urls.url, favicon_bitmaps ON favicon_bitmaps.id = (SELECT id FROM favicon_bitmaps WHERE favicon_bitmaps.icon_id = icon_mapping.icon_id ORDER BY width DESC LIMIT 1) WHERE (urls.title LIKE '%foo%' OR urls.url LIKE '%foo%') ORDER BY visit_count DESC, typed_count DESC, last_visit_time DESC;
s  o  f  detail
-  -  -  --------------------------------------------------------------------------------
0  0  0  SCAN TABLE urls
0  1  1  SEARCH TABLE icon_mapping USING INDEX icon_mapping_page_url_idx (page_url=?)
0  2  2  SEARCH TABLE favicon_bitmaps USING INTEGER PRIMARY KEY (rowid=?)
0  0  0  EXECUTE CORRELATED SCALAR SUBQUERY 1
1  0  0  SEARCH TABLE favicon_bitmaps USING INDEX favicon_bitmaps_icon_id (icon_id=?)
1  0  0  USE TEMP B-TREE FOR ORDER BY
0  0  0  USE TEMP B-TREE FOR ORDER BY

Compared to the previous query:

sqlite> explain query plan SELECT id,title,url FROM urls WHERE (title LIKE ? OR url LIKE ?) ORDER BY visit_count DESC, typed_count DESC, last_visit_time DESC;
s  o  f  detail
-  -  -  --------------------------------------------------------------------------------
0  0  0  SCAN TABLE urls
0  0  0  USE TEMP B-TREE FOR ORDER BY

The subqueries use indexes so should be fast, however there's one subquery per matching row which could be a problem if there are a lot of matches. Also the outer query itself is not bound by a limit, is this on purpose?

[tupton]

Thanks for this; it's pretty helpful. Sorry for the delay on looking further into this.

Also the outer query itself is not bound by a limit, is this on purpose?

That was done in #10 (by you) so I'm not totally familiar with that query, but I don't think there's a limit on either on purpose. We grab them all from sqlite up front and then they are transformed into Alfred items as needed. Perhaps we should put a hard limit on that query, but I've not seemed to have a problem with the amount of results without a limit.