Hi Both,
Thanks for your thoughts.
> SQLite has no DATETIME or BOOLEAN datatype
I'm aware that DATETIME (and BOOLEAN) isn't an actual datatype in
SQLite, but it functions exactly as you'd expect - I've used them
extensively in the past and never had a problem - I'm actually storing
ISO8601 strings and so it's simply a synonym for the TEXT - I find
having human-readable datetimes infinitely more practical for
development purposes. It's one of the things I most like about SQLite -
datetimes are ridiculously easy compared to "proper" databases.
> Drop the ORDER BY; it is useless in a view used in another query,
I figured sqlite would optimise it out if it didn't think it needed it
(it's needed by some of the other things that use that view). A quick
test seems to confirm it. It seems that the ORDER BY becomes the "3
Noop 5 4 0 00 " line in the EXPLAIN report - otherwise
the explain is identical.
> If SQLite thinks that an AUTOMATIC index is useful, you should
consider creating it explicitly: CREATE INDEX lookup_generid_id_idx ON
lookups(is_generic_flag, url_id);
It does make the EXPLAIN plan 6 lines shorter, but no apparent speed
difference unfortunately.
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:
CREATE INDEX url_id_source_id_idx ON urls (
url_id,
source_seed_id
);
CREATE INDEX source_id_url_id_idx ON urls (
url_id,
source_seed_id
);
SQLite will use the former (url_id, then source_seed_id), but it makes
absolutely no difference to the speed.
So I'm still stumped.
On 2018-03-18 22:30, Tim Streater wrote:
On 18 Mar 2018, at 21:48, Jonathan Moules <jonathan-li...@lightpear.com> wrote:
CREATE TABLE lookups (
url_id INTEGER REFERENCES urls (url_id),
retrieval_datetime DATETIME,
error_code INTEGER,
is_generic_flag BOOLEAN -- about one in 100 will have this
flagged
);
SQLite has no DATETIME or BOOLEAN datatype, see:
<http://www.sqlite.org/datatype3.html>
Given this, I don't know what your attempts to compare date/times will do.
Personally I store all date/times as seconds since the epoch; it simplifies
life a lot.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
