On 18 Mar 2018, at 11:13pm, Jonathan Moules <[email protected]>
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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
