strftime returns a text representation. So you didn't really change anything.
You need to use juliandays() as I said. And you want a REAL number...not integer...though SQLite doesn't really care what you call it. It's more for your own reference. You just added a bunch more strings increasing the size of your database...ergo it ran slower. 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 12:44 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] speeding up FTS4 On Sep 28, 2011, at 11:00 AM, Black, Michael (IS) wrote: > Your change to numeric date/time may not take a long as you think. > > > Took an hour and a half. Step 1: Alter all tables with datetime columns, converting those columns to integer; Step 2: Update all tables setting new datetime columns to unixtime UPDATE table SET new_column = strftime('%s', old_column); Step 3: CREATE INDEX uris_downloaded_on ON uris (u_downloaded_on); Step 4: Run the following query SELECT u.uri_id uri_id, u.uri uri, u.u_downloaded_on FROM fts_uri f JOIN uris u ON f.uri_id = u.uri_id JOIN feed_history fh ON u.feed_history_id = fh.feed_history_id JOIN feeds f ON fh.feed_id = f.feed_id JOIN projects p ON f.project_id = p.project_id WHERE p.project_id = 3 AND u.u_downloaded_on >= p.u_project_start AND fts_uri MATCH 'education school' ORDER BY u.uri_id, u_downloaded_on DESC; Terrible time. >> CPU Time: user 27.584849 sys 115.219293 Step 5: EXPLAIN QUERY PLAN (above SELECT query) 0|0|0|SCAN TABLE fts_uri AS f VIRTUAL TABLE INDEX 4: (~0 rows) 0|1|1|SEARCH TABLE uris AS u USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|2|2|SEARCH TABLE feed_history AS fh USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|3|3|SEARCH TABLE feeds AS f USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|4|4|SEARCH TABLE projects AS p USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|0|0|USE TEMP B-TREE FOR ORDER BY CPU Time: user 0.000099 sys 0.000008 Step 6: ANALYZE; Step 7: Run the above SELECT query again Terrible time. >> CPU Time: user 27.703538 sys 116.684390 This train is going nowhere. The times are actually worse than they were when I was using a non-text column for date time. By the way, have tried this on two machines -- the previous one was a dual-Xeon Xserve with 12 GB RAM. The current machine of choice is the top of the line iMac (quad core 3.4 GHz Intel Core i7) with 12 GB RAM and a 7200 RPM SATA drive. > 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. > > _______________________________________________ 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