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: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Puneet Kishor [punk.k...@gmail.com] 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users