Your change to numeric date/time may not take a long as you think.
drop any indexes on project_start and downloaded_on;
update projects set project_start=julianday(project_start);
update uris set downloaded_on=julianday(downloaded_on);
Recreate indexes.
Modify your code to insert julianday('now','localtime') instead of taking the
default current_timestamp.
I wasn't really aware before the the datetime functions stored as text all the
time...that's bad for searches...
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
________________________________
From: [email protected] [[email protected]] on
behalf of Puneet Kishor [[email protected]]
Sent: Wednesday, September 28, 2011 9:48 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] EXT : speeding up FTS4
On Sep 28, 2011, at 9:44 AM, Simon Slavin wrote:
>
> On 28 Sep 2011, at 3:41pm, Puneet Kishor wrote:
>
>> WHERE Datetime(u.downloaded_on) >= Datetime(p.project_start)
>
> Why are you doing 'Datetime' here ? Not only does the conversion take time,
> but it means you can't usefully index either of those two columns.
>
> Can you instead store your stamps in a format which is readily sortable ?
> Either in text form or as julian days.
Could I? Sure, if I had known better. Should I? I would be happy to create a
new column, convert the values to julian days, and try that, but on a 27 GB db,
that would take a bit of a while.
But, if I understood [http://www.sqlite.org/datatype3.html] correctly, there
really is no such thing as DATETIME value. Internally, it is stored as TEXT
anyway.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
