On 18 Mar 2018, at 11:13pm, Jonathan Moules <jonathan-li...@lightpear.com> wrote:
> Given there's such a small amount of data at this point, I suspect the issue > is more related to the recursion. I've tried creating these two indexes to > facilicate that Nice idea but I can see why it's not working. You have an underlying problem: the format you're using to store your data makes it extremely difficult to extract the figures you want. Quan Yong Zhai has the best idea I've seen: get rid of almost ¾ of the work you're doing by storing the last retrieval date in your "urls" table. As an alternative, store the start your retrieval process by JOINing the two tables together. Consider the result of this query SELECT url_id, lookups.error_code FROM urls JOIN lookups ON lookups.url_id = urls.url_id AND lookup.retrieval_datetime = urls.retrieval_datetime WHERE DATETIME(urls.retrieval_datetime) > DATETIME('now', '-5 days') and figure out what you would add to that to get your desired result. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users