Re: [sqlite] sort a contenated value as inserting
On Sep 29, 2011, at 8:35 AM, guiz wrote: > but I want t2.x sorted as '2, 6' , '3, 5', '2, 3' instead of '6, 2', '5, 3', > '2, 3'. well, then do do: insert into t2(x) select (b || ', ' || a) from t; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sort a contenated value as inserting
I have as the followings...
create table t ([a] varchar(3), [b] varchar(3));
insert into t(a, b) values('6', '2');
insert into t(a, b) values('5', '3');
insert into t(a, b) values('2', '3');
create table t2([x] varchar(9));
insert into t2(x) select (a || ', ' || b) from t;
but I want t2.x sorted as '2, 6' , '3, 5', '2, 3' instead of '6, 2', '5, 3',
'2, 3'.
Please, help. Thank's.
--
View this message in context:
http://old.nabble.com/sort-a-contenated-value-as-inserting-tp32550882p32550882.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with using WAL journal mode in embedded system (disk I/O error)
On Wed, Sep 28, 2011 at 2:40 PM, Korey Calmettes wrote:
> Richard,
>
> That is my conclusion as well. From what I have read online, JFFS
> doesn't support mmap. Would it be possible to place those files in
> tmpfs instead? Is there logic to do that?
>
An early implementation of WAL did just that. But then we realized that
doing it that way won't work from a chroot jail - which is why we changed it
to the current mechanism of using a file in the same directory as the
database.
If you recompile with SQLITE_SHM_DIRECTORY set to a directory name (that is
perhaps a tmpfs mount) then the old behavior is active again and the
WAL-index will be put in the named directory rather than in the same
directory as the database file.
http://www.sqlite.org/src/artifact/10e0c4dcdbec8?ln=3827
>
> Korey
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: September 26, 2011 11:55 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Problem with using WAL journal mode in embedded
> system (disk I/O error)
>
> On Mon, Sep 26, 2011 at 2:48 PM, Korey Calmettes
> wrote:
>
> > Richard,
> >
> > Thanks for the tip of recreating the experiment in a separate
> directory.
> > However the same error occurs.
> >
> > sqlite> .tables
> > (5386) os_unix.c:28414: (22) mmap(/data/test.db-shm) -
> > (5386) statement aborts at 109: [SELECT name FROM sqlite_master WHERE
> > type IN ('table','view') AND name NOT LIKE 'sqlite_%' UNION ALL SELECT
>
> > name FROM sqlite_temp_master WHERE type IN ('table','view') ORDER BY
> > 1] disk I/O error
> > Error: disk I/O error
> > sqlite>
> >
> > Any additional ideas why mmap would be failing?
> >
> >
> Perhaps your kernel doesn't support mmap() on JFFS filesystems? (That's
> just a guess.)
>
>
>
> > Korey
> >
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org
> > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
> > Sent: September 26, 2011 11:36 AM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Problem with using WAL journal mode in embedded
> > system (disk I/O error)
> >
> > On Mon, Sep 26, 2011 at 2:21 PM, Korey Calmettes
> > wrote:
> >
> > > Thanks for responding so quickly.
> > >
> > > I performed the same general experiment. Here is the output when
> > > the error occurred.
> > >
> > > sqlite> .tables
> > > (5386) os_unix.c:28414: (22) mmap(//test.db-shm) -
> > >
> >
> > Looks like a mmap() call is failing here:
> > http://www.sqlite.org/src/artifact/10e0c4dcdb?ln=4009-4011
> >
> > Looks like you are using a database in the root of the filesystem
> > ("/test.db"). Do you get the same error if you put the database file
> > in a directory someplace?
> >
> >
> > > (5386) statement aborts at 109: [SELECT name FROM sqlite_master
> > > WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' UNION
> > > ALL SELECT
> >
> > > name FROM sqlite_temp_master WHERE type IN ('table','view') ORDER BY
>
> > > 1] disk I/O error
> > > Error: disk I/O error
> > > sqlite>
> > >
> > > Any ideas?
> > >
> > > Korey
> > >
> > >
> > > -Original Message-
> > > From: sqlite-users-boun...@sqlite.org
> > > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
> > > Sent: September 26, 2011 8:38 AM
> > > To: General Discussion of SQLite Database
> > > Subject: Re: [sqlite] Problem with using WAL journal mode in
> > > embedded system (disk I/O error)
> > >
> > > On Mon, Sep 26, 2011 at 11:30 AM, Korey Calmettes
> > > wrote:
> > >
> > > > ~ # sqlite3 test.db
> > > > SQLite version 3.7.7.1 2011-06-28 17:39:05 Enter ".help" for
> > > > instructions Enter SQL statements terminated with a ";"
> > > > sqlite> .tables
> > > > test
> > > > sqlite> pragma journal_mode=wal;
> > > > wal
> > > > sqlite> .tables
> > > > Error: disk I/O error
> > > > sqlite>
> > > >
> > > >
> > > > Any ideas on what may be causing this error and any suggestions
> > > > for a work-around?
> > > >
> > >
> > > Type:
> > >
> > >.log stdout
> > >
> > > at the beginning of this session and run your experiment again
> please.
> > > The extra output might give some clues.
> > >
> > >
> > > --
> > > D. Richard Hipp
> > > d...@sqlite.org
> > > ___
> > > 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
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > 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.o
Re: [sqlite] speeding up FTS4
On Sep 28, 2011, at 4:18 PM, Black, Michael (IS) wrote: > I have no idea if this would work...but...here's some more thoughts... > > > > #1 How long does this take: > > select count(*) from fts_uri match 'education school'; > > > > #2 Create a view on uris with just what you need and use that in your join > (I'm guessing that uri_content takes up most of your database space). > > > > create view v_uris as select uri_id,feed_history_id from uri; > > > .. I did a query on just the fts table and got the answers relatively quickly. Not instantly, but very fast compared to all the attempts so far. So, assuming that the bottleneck is the multiple JOINs to get the data for the correct project_id, I created a temp table with all that JOIN nonsense sqlite> CREATE TEMP TABLE tmp_uris AS SELECT u.uri_id, u.uri uri, u.u_downloaded_on ...> FROM projects p ...> JOIN feeds f ON f.project_id = p.project_id ...> JOIN feed_history fh ON f.feed_id = fh.feed_id ...> JOIN uris u ON fh.feed_history_id = u.feed_history_id ...> WHERE p.project_id = 3 AND u.u_downloaded_on >= p.u_project_start; CPU Time: user 16.369556 sys 81.393235 sqlite> EXPLAIN QUERY PLAN SELECT u.uri_id uri_id, u.uri uri, u.u_downloaded_on, ...> Snippet(fts_uri, '', '', '…', -1, 64) snippet ...> FROM fts_uri f JOIN tmp_uris u ON f.uri_id = u.uri_id ...> WHERE fts_uri MATCH 'education school' ...> ORDER BY u.uri_id, u_downloaded_on DESC; 0|0|0|SCAN TABLE fts_uri AS f VIRTUAL TABLE INDEX 4: (~0 rows) 0|1|1|SEARCH TABLE tmp_uris AS u USING INDEX tmp_uris_uri_id (uri_id=?) (~10 rows) 0|0|0|USE TEMP B-TREE FOR ORDER BY CPU Time: user 0.86 sys 0.06 and yet sqlite> SELECT u.uri_id uri_id, u.uri uri, u.u_downloaded_on, ...> Snippet(fts_uri, '', '', '…', -1, 64) snippet ...> FROM fts_uri f JOIN tmp_uris u ON f.uri_id = u.uri_id ...> WHERE fts_uri MATCH 'education school' ...> ORDER BY u.uri_id, u_downloaded_on DESC; CPU Time: user 21.871541 sys 26.414337 A lot better, but simply not usable for a web application. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] speeding up FTS4
I have no idea if this would work...but...here's some more thoughts... #1 How long does this take: select count(*) from fts_uri match 'education school'; #2 Create a view on uris with just what you need and use that in your join (I'm guessing that uri_content takes up most of your database space). create view v_uris as select uri_id,feed_history_id from uri; #3 Move your uri_content to another database and attach it. Again I'm assuming that's your big data field. That will seperate the paging needed to walk through uris. 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 2:54 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] speeding up FTS4 On Sep 28, 2011, at 2:41 PM, Black, Michael (IS) wrote: > What happens if you create an index on uris(feed_history_id) > > > Yeah, I noticed that lacking as well. sqlite> EXPLAIN QUERY PLAN SELECT u.uri_id ...> FROM projects p ...> JOIN feeds f ON f.project_id = p.project_id ...> JOIN feed_history fh ON f.feed_id = fh.feed_id ...> JOIN uris u ON fh.feed_history_id = u.feed_history_id ...> WHERE p.project_id = 3; 0|0|0|SEARCH TABLE projects AS p USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|1|3|SCAN TABLE uris AS u (~46608 rows) 0|2|2|SEARCH TABLE feed_history AS fh USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|3|1|SEARCH TABLE feeds AS f USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) CPU Time: user 0.79 sys 0.14 sqlite> CREATE INDEX uris_feed_history_id ON uris (feed_history_id); CPU Time: user 12.766977 sys 82.766372 sqlite> EXPLAIN QUERY PLAN SELECT u.uri_id ...> FROM projects p ...> JOIN feeds f ON f.project_id = p.project_id ...> JOIN feed_history fh ON f.feed_id = fh.feed_id ...> JOIN uris u ON fh.feed_history_id = u.feed_history_id ...> WHERE p.project_id = 3; 0|0|0|SEARCH TABLE projects AS p USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|1|1|SCAN TABLE feeds AS f (~7 rows) 0|2|2|SEARCH TABLE feed_history AS fh USING AUTOMATIC COVERING INDEX (feed_id=?) (~5 rows) 0|3|3|SEARCH TABLE uris AS u USING COVERING INDEX uris_feed_history_id (feed_history_id=?) (~10 rows) CPU Time: user 0.000123 sys 0.15 But, no joy. 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; .. CPU Time: user 28.599581 sys 108.518205 > > > > > 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 2:00 PM > To: General Discussion of SQLite Database > Subject: EXT :Re: [sqlite] speeding up FTS4 > > > On Sep 28, 2011, at 1:14 PM, Black, Michael (IS) wrote: > >> strftime returns a text representation. So you didn't really change >> anything. >> > > > That's not true at all. I added u_downloaded_on (u_ for unixtime) > > CREATE TABLE uris ( >uri_id INTEGER PRIMARY KEY, >uri TEXT, >uri_html TEXT, >uri_content TEXT, >downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP, >feed_history_id INTEGER, >u_downloaded_on INTEGER > ); > > sqlite> SELECT u_downloaded_on FROM uris LIMIT 5; > 1306450769 > 1306450769 > 1306450770 > 1306450774 > 1306450776 > > >> 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. >> >> > > > If I understand correctly, the *size* of the database should not matter. Or, > at least not matter as much. Imagine a database with one table with only one > row but with so much content in that row that it is 27 GB vs. a database with > a few million rows, each with small amount of content but together totaling > 27 GB. > > What should matter is using the indexes correctly. In this case, my query > plan shows that I am hitting all the indexes. But, I think I am getting > closer to understanding this. > > I started backward with > > sqlite> SELECT p.project_id > ...> FROM projects p > ...> WHERE p.project_id = 3; > 3 > CPU Time: user 0.80 sys 0.89 > > sqlite> SELECT f.feed_id, p.project_id > ...> FROM projects p JOIN feeds f ON f.project_id = p.project_id > ...> WHERE p.project_id = 3; > .. > CPU Time: user 0.000239 sys 0.000170 > > SELECT fh.feed_his
[sqlite] attach readonly db to main db that is readwrite
My primary database is opened for read/write. I use the ATTACH command to attach a second database that lives in a read-only filesystem. It appears to mostly work, but is there anything I should be aware of or concerned about? One thing I noticed is if I run "ANALYZE" once the read-only is attached, then ANALYZE fails, perhaps for obvious reasons... Is there a way to ATTACH and explicitly specify a readonly flag? I'm not doing this yet, but what about the other way around - readonly main db, with attached read/write databases? Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite shell's .import does not work with attached db's, add wiki note?
On Wed, Sep 28, 2011 at 2:21 PM, Roger Binns wrote: > On 09/26/2011 06:03 PM, yary wrote: >> I noticed that the sqlite shell won't ".import" into an attached database: > > You'll be pleased to know that the team have now fixed the bug. It will be > in the next SQLite release. great, thanks! -y ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] speeding up FTS4
On 09/28/11 21:55, Puneet Kishor wrote: Perhaps, but I have inserted that in my table where the column is INTEGER. sqlite> SELECT typeof(u_downloaded_on) FROM uris LIMIT 1; integer -- OK! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] speeding up FTS4
On Sep 28, 2011, at 2:26 PM, Roger Andersson wrote:
> On 09/28/11 21:10, Black, Michael (IS) wrote:
>>
>> 'scuse meI was wrong (again)...I guess strftime does return an
>> integerseems to me that belies the name as it's a mismatch to the unix
>> function.
>>
>>
> ?
> SQLite version 3.7.8 2011-09-19 14:49:19
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select typeof(strftime('%s','now','localtime'));
> text
>
Perhaps, but I have inserted that in my table where the column is INTEGER.
sqlite> SELECT typeof(u_downloaded_on) FROM uris LIMIT 1;
integer
--
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] speeding up FTS4
On Sep 28, 2011, at 2:41 PM, Black, Michael (IS) wrote: > What happens if you create an index on uris(feed_history_id) > > > Yeah, I noticed that lacking as well. sqlite> EXPLAIN QUERY PLAN SELECT u.uri_id ...> FROM projects p ...> JOIN feeds f ON f.project_id = p.project_id ...> JOIN feed_history fh ON f.feed_id = fh.feed_id ...> JOIN uris u ON fh.feed_history_id = u.feed_history_id ...> WHERE p.project_id = 3; 0|0|0|SEARCH TABLE projects AS p USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|1|3|SCAN TABLE uris AS u (~46608 rows) 0|2|2|SEARCH TABLE feed_history AS fh USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|3|1|SEARCH TABLE feeds AS f USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) CPU Time: user 0.79 sys 0.14 sqlite> CREATE INDEX uris_feed_history_id ON uris (feed_history_id); CPU Time: user 12.766977 sys 82.766372 sqlite> EXPLAIN QUERY PLAN SELECT u.uri_id ...> FROM projects p ...> JOIN feeds f ON f.project_id = p.project_id ...> JOIN feed_history fh ON f.feed_id = fh.feed_id ...> JOIN uris u ON fh.feed_history_id = u.feed_history_id ...> WHERE p.project_id = 3; 0|0|0|SEARCH TABLE projects AS p USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|1|1|SCAN TABLE feeds AS f (~7 rows) 0|2|2|SEARCH TABLE feed_history AS fh USING AUTOMATIC COVERING INDEX (feed_id=?) (~5 rows) 0|3|3|SEARCH TABLE uris AS u USING COVERING INDEX uris_feed_history_id (feed_history_id=?) (~10 rows) CPU Time: user 0.000123 sys 0.15 But, no joy. 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; .. CPU Time: user 28.599581 sys 108.518205 > > > > > 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 2:00 PM > To: General Discussion of SQLite Database > Subject: EXT :Re: [sqlite] speeding up FTS4 > > > On Sep 28, 2011, at 1:14 PM, Black, Michael (IS) wrote: > >> strftime returns a text representation. So you didn't really change >> anything. >> > > > That's not true at all. I added u_downloaded_on (u_ for unixtime) > > CREATE TABLE uris ( >uri_id INTEGER PRIMARY KEY, >uri TEXT, >uri_html TEXT, >uri_content TEXT, >downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP, >feed_history_id INTEGER, >u_downloaded_on INTEGER > ); > > sqlite> SELECT u_downloaded_on FROM uris LIMIT 5; > 1306450769 > 1306450769 > 1306450770 > 1306450774 > 1306450776 > > >> 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. >> >> > > > If I understand correctly, the *size* of the database should not matter. Or, > at least not matter as much. Imagine a database with one table with only one > row but with so much content in that row that it is 27 GB vs. a database with > a few million rows, each with small amount of content but together totaling > 27 GB. > > What should matter is using the indexes correctly. In this case, my query > plan shows that I am hitting all the indexes. But, I think I am getting > closer to understanding this. > > I started backward with > > sqlite> SELECT p.project_id > ...> FROM projects p > ...> WHERE p.project_id = 3; > 3 > CPU Time: user 0.80 sys 0.89 > > sqlite> SELECT f.feed_id, p.project_id > ...> FROM projects p JOIN feeds f ON f.project_id = p.project_id > ...> WHERE p.project_id = 3; > .. > CPU Time: user 0.000239 sys 0.000170 > > SELECT fh.feed_history_id, f.feed_id, p.project_id > ...> FROM projects p > ...> JOIN feeds f ON f.project_id = p.project_id > ...> JOIN feed_history fh ON f.feed_id = fh.feed_id > ...> WHERE p.project_id = 3; > .. > CPU Time: user 0.008491 sys 0.008054 > > SELECT u.uri_id, fh.feed_history_id, f.feed_id, p.project_id > ...> FROM projects p > ...> JOIN feeds f ON f.project_id = p.project_id > ...> JOIN feed_history fh ON f.feed_id = fh.feed_id > ...> JOIN uris u ON fh.feed_history_id = u.feed_history_id > ...> WHERE p.project_id = 3; > > > BOOM! Adding that last table makes my query way too slow. > > Taking out the uris table and querying only the fts table > > sqlite> SELECT uri_id > ...> FROM fts_uri > ...> WHERE fts_uri MATCH 'education school'; > > > starts producing the results immediately, but gives me back way too many > matches. I am now getting
Re: [sqlite] speeding up FTS4
What happens if you create an index on uris(feed_history_id)
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 2:00 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] speeding up FTS4
On Sep 28, 2011, at 1:14 PM, Black, Michael (IS) wrote:
> strftime returns a text representation. So you didn't really change anything.
>
That's not true at all. I added u_downloaded_on (u_ for unixtime)
CREATE TABLE uris (
uri_id INTEGER PRIMARY KEY,
uri TEXT,
uri_html TEXT,
uri_content TEXT,
downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP,
feed_history_id INTEGER,
u_downloaded_on INTEGER
);
sqlite> SELECT u_downloaded_on FROM uris LIMIT 5;
1306450769
1306450769
1306450770
1306450774
1306450776
> 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.
>
>
If I understand correctly, the *size* of the database should not matter. Or, at
least not matter as much. Imagine a database with one table with only one row
but with so much content in that row that it is 27 GB vs. a database with a few
million rows, each with small amount of content but together totaling 27 GB.
What should matter is using the indexes correctly. In this case, my query plan
shows that I am hitting all the indexes. But, I think I am getting closer to
understanding this.
I started backward with
sqlite> SELECT p.project_id
...> FROM projects p
...> WHERE p.project_id = 3;
3
CPU Time: user 0.80 sys 0.89
sqlite> SELECT f.feed_id, p.project_id
...> FROM projects p JOIN feeds f ON f.project_id = p.project_id
...> WHERE p.project_id = 3;
..
CPU Time: user 0.000239 sys 0.000170
SELECT fh.feed_history_id, f.feed_id, p.project_id
...> FROM projects p
...> JOIN feeds f ON f.project_id = p.project_id
...> JOIN feed_history fh ON f.feed_id = fh.feed_id
...> WHERE p.project_id = 3;
..
CPU Time: user 0.008491 sys 0.008054
SELECT u.uri_id, fh.feed_history_id, f.feed_id, p.project_id
...> FROM projects p
...> JOIN feeds f ON f.project_id = p.project_id
...> JOIN feed_history fh ON f.feed_id = fh.feed_id
...> JOIN uris u ON fh.feed_history_id = u.feed_history_id
...> WHERE p.project_id = 3;
BOOM! Adding that last table makes my query way too slow.
Taking out the uris table and querying only the fts table
sqlite> SELECT uri_id
...> FROM fts_uri
...> WHERE fts_uri MATCH 'education school';
starts producing the results immediately, but gives me back way too many
matches. I am now getting all the matches, but I want only the matches for
project id = 3 or matches throttled by u_downloaded_on (both of which are
achievable only via a JOIN with uris table).
>
> 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
Re: [sqlite] speeding up FTS4
On Sep 28, 2011, at 9:00 PM, Puneet Kishor wrote: > If I understand correctly, the *size* of the database should not matter. Or, > at least not matter as much. So she said. But contrary to popular believe, size does matter. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] speeding up FTS4
On 09/28/11 21:10, Black, Michael (IS) wrote:
'scuse meI was wrong (again)...I guess strftime does return an
integerseems to me that belies the name as it's a mismatch to the
unix function.
?
SQLite version 3.7.8 2011-09-19 14:49:19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select typeof(strftime('%s','now','localtime'));
text
/Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] speeding up FTS4
'scuse meI was wrong (again)...I guess strftime does return an
integerseems to me that belies the name as it's a mismatch to the unix
function.
select strftime('%s','now');
1317236583
But I think you may want:
strftime('%s','now','unixepoch','localtime');
That works with the default datetime() then when you select it.
sqlite> select datetime(strftime('%s','now'),'unixepoch','localtime');
2011-09-28 14:05:09
So...given that you actually stuck integers into your table I'm a bit surprised
that it took longer.
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 Roger Andersson [r...@telia.com]
Sent: Wednesday, September 28, 2011 1:52 PM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] speeding up FTS4
On 09/28/11 20:14, Black, Michael (IS) wrote:
> 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.
>
Assuming that second resolution is sufficient.
Would
UPDATE table SET new_column = cast(strftime('%s', old_column) as integer);
make any difference?
/Roger
___
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
Re: [sqlite] speeding up FTS4
On Sep 28, 2011, at 1:14 PM, Black, Michael (IS) wrote:
> strftime returns a text representation. So you didn't really change anything.
>
That's not true at all. I added u_downloaded_on (u_ for unixtime)
CREATE TABLE uris (
uri_id INTEGER PRIMARY KEY,
uri TEXT,
uri_html TEXT,
uri_content TEXT,
downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP,
feed_history_id INTEGER,
u_downloaded_on INTEGER
);
sqlite> SELECT u_downloaded_on FROM uris LIMIT 5;
1306450769
1306450769
1306450770
1306450774
1306450776
> 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.
>
>
If I understand correctly, the *size* of the database should not matter. Or, at
least not matter as much. Imagine a database with one table with only one row
but with so much content in that row that it is 27 GB vs. a database with a few
million rows, each with small amount of content but together totaling 27 GB.
What should matter is using the indexes correctly. In this case, my query plan
shows that I am hitting all the indexes. But, I think I am getting closer to
understanding this.
I started backward with
sqlite> SELECT p.project_id
...> FROM projects p
...> WHERE p.project_id = 3;
3
CPU Time: user 0.80 sys 0.89
sqlite> SELECT f.feed_id, p.project_id
...> FROM projects p JOIN feeds f ON f.project_id = p.project_id
...> WHERE p.project_id = 3;
..
CPU Time: user 0.000239 sys 0.000170
SELECT fh.feed_history_id, f.feed_id, p.project_id
...> FROM projects p
...> JOIN feeds f ON f.project_id = p.project_id
...> JOIN feed_history fh ON f.feed_id = fh.feed_id
...> WHERE p.project_id = 3;
..
CPU Time: user 0.008491 sys 0.008054
SELECT u.uri_id, fh.feed_history_id, f.feed_id, p.project_id
...> FROM projects p
...> JOIN feeds f ON f.project_id = p.project_id
...> JOIN feed_history fh ON f.feed_id = fh.feed_id
...> JOIN uris u ON fh.feed_history_id = u.feed_history_id
...> WHERE p.project_id = 3;
BOOM! Adding that last table makes my query way too slow.
Taking out the uris table and querying only the fts table
sqlite> SELECT uri_id
...> FROM fts_uri
...> WHERE fts_uri MATCH 'education school';
starts producing the results immediately, but gives me back way too many
matches. I am now getting all the matches, but I want only the matches for
project id = 3 or matches throttled by u_downloaded_on (both of which are
achievable only via a JOIN with uris table).
>
> 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.99 sys 0.08
>
> Step 6: ANALYZE;
>
> Step 7: Run the above SELECT query again
>
> Terrible time.
>>> CPU Time: user 27.703538 sys 116.684390
>
>
>
> This train is goi
Re: [sqlite] speeding up FTS4
On 09/28/11 20:14, Black, Michael (IS) wrote:
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.
Assuming that second resolution is sufficient.
Would
UPDATE table SET new_column = cast(strftime('%s', old_column) as integer);
make any difference?
/Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with using WAL journal mode in embedded system (disk I/O error)
Richard,
That is my conclusion as well. From what I have read online, JFFS
doesn't support mmap. Would it be possible to place those files in
tmpfs instead? Is there logic to do that?
Korey
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
Sent: September 26, 2011 11:55 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problem with using WAL journal mode in embedded
system (disk I/O error)
On Mon, Sep 26, 2011 at 2:48 PM, Korey Calmettes
wrote:
> Richard,
>
> Thanks for the tip of recreating the experiment in a separate
directory.
> However the same error occurs.
>
> sqlite> .tables
> (5386) os_unix.c:28414: (22) mmap(/data/test.db-shm) -
> (5386) statement aborts at 109: [SELECT name FROM sqlite_master WHERE
> type IN ('table','view') AND name NOT LIKE 'sqlite_%' UNION ALL SELECT
> name FROM sqlite_temp_master WHERE type IN ('table','view') ORDER BY
> 1] disk I/O error
> Error: disk I/O error
> sqlite>
>
> Any additional ideas why mmap would be failing?
>
>
Perhaps your kernel doesn't support mmap() on JFFS filesystems? (That's
just a guess.)
> Korey
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: September 26, 2011 11:36 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Problem with using WAL journal mode in embedded
> system (disk I/O error)
>
> On Mon, Sep 26, 2011 at 2:21 PM, Korey Calmettes
> wrote:
>
> > Thanks for responding so quickly.
> >
> > I performed the same general experiment. Here is the output when
> > the error occurred.
> >
> > sqlite> .tables
> > (5386) os_unix.c:28414: (22) mmap(//test.db-shm) -
> >
>
> Looks like a mmap() call is failing here:
> http://www.sqlite.org/src/artifact/10e0c4dcdb?ln=4009-4011
>
> Looks like you are using a database in the root of the filesystem
> ("/test.db"). Do you get the same error if you put the database file
> in a directory someplace?
>
>
> > (5386) statement aborts at 109: [SELECT name FROM sqlite_master
> > WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' UNION
> > ALL SELECT
>
> > name FROM sqlite_temp_master WHERE type IN ('table','view') ORDER BY
> > 1] disk I/O error
> > Error: disk I/O error
> > sqlite>
> >
> > Any ideas?
> >
> > Korey
> >
> >
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org
> > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
> > Sent: September 26, 2011 8:38 AM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Problem with using WAL journal mode in
> > embedded system (disk I/O error)
> >
> > On Mon, Sep 26, 2011 at 11:30 AM, Korey Calmettes
> > wrote:
> >
> > > ~ # sqlite3 test.db
> > > SQLite version 3.7.7.1 2011-06-28 17:39:05 Enter ".help" for
> > > instructions Enter SQL statements terminated with a ";"
> > > sqlite> .tables
> > > test
> > > sqlite> pragma journal_mode=wal;
> > > wal
> > > sqlite> .tables
> > > Error: disk I/O error
> > > sqlite>
> > >
> > >
> > > Any ideas on what may be causing this error and any suggestions
> > > for a work-around?
> > >
> >
> > Type:
> >
> >.log stdout
> >
> > at the beginning of this session and run your experiment again
please.
> > The extra output might give some clues.
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > 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
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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
>
--
D. Richard Hipp
d...@sqlite.org
___
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
Re: [sqlite] Sqlite shell's .import does not work with attached db's, add wiki note?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/26/2011 06:03 PM, yary wrote: > I noticed that the sqlite shell won't ".import" into an attached database: You'll be pleased to know that the team have now fixed the bug. It will be in the next SQLite release. http://www.sqlite.org/src/info/d1d84037b9 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk6DZY4ACgkQmOOfHg372QRD6wCfVsqp+hqQjICe443M1HLoHoWQ VUkAnROp2ycwrlermzclYAWxYGklEPQz =k7wx -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] speeding up FTS4
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.99 sys 0.08
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
Re: [sqlite] speeding up FTS4
On 28 Sep 2011, at 6:44pm, Puneet Kishor wrote: > 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.99 sys 0.08 I'm not familiar with the output of EXPLAIN QUERY PLAN. 'SEARCH' means it's using an index ? And SCAN means it isn't ? Can anyone suggest a good INDEX or two ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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.99 sys 0.08
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
Re: [sqlite] speeding up FTS4
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
Re: [sqlite] Still chasing "database schema has changed"
On Sep 28, 2011, at 1:25 AM, Richard Hipp wrote: > Hence I ask: have you actually measured the difference? Please join the BAAG party :) http://www.battleagainstanyguess.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXT : speeding up FTS4
On 28 Sep 2011, at 3:52pm, Petite Abeille wrote:
> On Sep 28, 2011, at 4:48 PM, Puneet Kishor wrote:
>
>> 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.
>
> Or as a number. Your choice:
>
> • TEXT as ISO8601 strings ("-MM-DD HH:MM:SS.SSS").
> • REAL as Julian day numbers, the number of days since noon in
> Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian
> calendar.
> • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00
> UTC.
>
> Considering the amount of data you have, perhaps something like a unix time
> would be more, hmmm, more frugal.
Of course, if he is consistent for any of these he can just replace
> WHERE Datetime(u.downloaded_on) >= Datetime(p.project_start)
with
WHERE u.downloaded_on >= p.project_start
and make indexes which include the columns. No conversion needed. The only
reason to need conversion is if the source data is in one format in some rows
and another format in other rows.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] speeding up FTS4
Sounds like you may just be hitting disk i/o. Your "sys" numbers seem to indicate that. How much memory does your machine have? How much time does each WHERE clause take? select count(*) from project where project_id = 3; select count(*) from fts_uri MATCH 'education,school'; select count(*) from project as p,fts_uri as u where DateTime(u.downloaded_on) >= DateTime(p.project_start); (I don't think you need Datetime at all...but I doubt it makes a lot of differencetest it)...should get the same count. select count(*) from project as p,uris as u where u.downloaded_on >= p.project_start; 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:41 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] EXT : speeding up FTS4 On Sep 28, 2011, at 8:02 AM, Black, Michael (IS) wrote: > Have you done "ANALYZE"? That might help. > > Also...try to arrange your joins based on record count (both high-to-low and > low-to-high) and see what difference it makes. > > Since you have only one WHERE clause I'm guessing having project_ids as the > first join makes sense. > > So, I ran ANALYZE. Then, with the query as is, I got CPU Time: user 24.742481 sys 79.120486 If I flip the WHERE clause to WHERE Datetime(u.downloaded_on) >= Datetime(p.project_start) AND p.project_id = 3 AND fts_uri MATCH 'education school' I still get CPU Time: user 24.726792 sys 79.240780 Yesterday, in desperation, I tried to pare down my fts_uri table which brought the file size to 17 GB, but ended up creating a "malformed disk image" whatever the heck that means. Thankfully I had a backup (clever boy). So, I am now back with a 27 GB file, and a query that takes forever thereby locking up my web app. > > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Puneet Kishor [punk.k...@gmail.com] > Sent: Tuesday, September 27, 2011 5:46 PM > To: General Discussion of SQLite Database > Subject: EXT :[sqlite] speeding up FTS4 > > > I have > >CREATE TABLE uris ( >uri_id INTEGER PRIMARY KEY, >uri TEXT, >uri_content TEXT, >downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP, >feed_history_id INTEGER >); > > with 46608 rows > >CREATE TABLE feed_history ( >feed_history_id INTEGER PRIMARY KEY, >feed_id INTEGER, >scanned_on DATETIME DEFAULT CURRENT_TIMESTAMP >); > > with 3276 rows > >CREATE TABLE feeds (feed_id INTEGER PRIMARY KEY, feed_uri TEXT, project_id > INTEGER); > > with 79 rows > >CREATE TABLE projects (project_id INTEGER PRIMARY KEY, project_name TEXT); > > with 3 rows > >CREATE INDEX idx_uris_downloaded_on ON uris (downloaded_on); >CREATE VIRTUAL TABLE fts_uri USING fts4 (uri_id, uri_content); > > The database file is about 27 GB. > > > The following query takes (CPU Time: user 23.952698 sys 73.188765) returning > 46608 rows > >SELECT u.uri_id uri_id, u.uri uri, u.downloaded_on, >Snippet(fts_uri, '', '', '…', -1, > 64) snippet >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 Datetime(u.downloaded_on) >= Datetime(p.project_start) >AND fts_uri MATCH 'education,school' >ORDER BY u.uri_id, downloaded_on DESC; > > > The EXPLAIN QUERY PLAN for the above query tells me > >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 > > > Is there anything I can do to speed this up? > > -- > Puneet Kishor ___ 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
Re: [sqlite] EXT : speeding up FTS4
On 28 Sep 2011, at 3:48pm, Puneet Kishor wrote: > 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. You only have to do it once, you can do it overnight, and you can pick which night you do it. You can store them as INTEGER or REAL, which is far faster to search than TEXT. And the alternative is to do some extra work every time someone uses that SELECT or anything like it. It also means you can usefully put those columns in an INDEX. Speeding up SELECTs is what INDEXes are all about. What you have done is the equivalent of collecting all the knowledge of the world and putting it unsorted in a huge warehouse. Every time anyone wants something they have to wade through, on average, half the warehouse before they find it. > 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. If you want to see what value is actually being stored just SELECT it without converting to Datatype and see what you get. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXT : speeding up FTS4
On Sep 28, 2011, at 4:48 PM, Puneet Kishor wrote:
> 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.
Or as a number. Your choice:
• TEXT as ISO8601 strings ("-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in
Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian
calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00
UTC.
Considering the amount of data you have, perhaps something like a unix time
would be more, hmmm, more frugal.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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
Re: [sqlite] EXT : speeding up FTS4
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. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXT : speeding up FTS4
On Sep 28, 2011, at 8:02 AM, Black, Michael (IS) wrote: > Have you done "ANALYZE"? That might help. > > Also...try to arrange your joins based on record count (both high-to-low and > low-to-high) and see what difference it makes. > > Since you have only one WHERE clause I'm guessing having project_ids as the > first join makes sense. > > So, I ran ANALYZE. Then, with the query as is, I got CPU Time: user 24.742481 sys 79.120486 If I flip the WHERE clause to WHERE Datetime(u.downloaded_on) >= Datetime(p.project_start) AND p.project_id = 3 AND fts_uri MATCH 'education school' I still get CPU Time: user 24.726792 sys 79.240780 Yesterday, in desperation, I tried to pare down my fts_uri table which brought the file size to 17 GB, but ended up creating a "malformed disk image" whatever the heck that means. Thankfully I had a backup (clever boy). So, I am now back with a 27 GB file, and a query that takes forever thereby locking up my web app. > > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Puneet Kishor [punk.k...@gmail.com] > Sent: Tuesday, September 27, 2011 5:46 PM > To: General Discussion of SQLite Database > Subject: EXT :[sqlite] speeding up FTS4 > > > I have > >CREATE TABLE uris ( >uri_id INTEGER PRIMARY KEY, >uri TEXT, >uri_content TEXT, >downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP, >feed_history_id INTEGER >); > > with 46608 rows > >CREATE TABLE feed_history ( >feed_history_id INTEGER PRIMARY KEY, >feed_id INTEGER, >scanned_on DATETIME DEFAULT CURRENT_TIMESTAMP >); > > with 3276 rows > >CREATE TABLE feeds (feed_id INTEGER PRIMARY KEY, feed_uri TEXT, project_id > INTEGER); > > with 79 rows > >CREATE TABLE projects (project_id INTEGER PRIMARY KEY, project_name TEXT); > > with 3 rows > >CREATE INDEX idx_uris_downloaded_on ON uris (downloaded_on); >CREATE VIRTUAL TABLE fts_uri USING fts4 (uri_id, uri_content); > > The database file is about 27 GB. > > > The following query takes (CPU Time: user 23.952698 sys 73.188765) returning > 46608 rows > >SELECT u.uri_id uri_id, u.uri uri, u.downloaded_on, >Snippet(fts_uri, '', '', '…', -1, > 64) snippet >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 Datetime(u.downloaded_on) >= Datetime(p.project_start) >AND fts_uri MATCH 'education,school' >ORDER BY u.uri_id, downloaded_on DESC; > > > The EXPLAIN QUERY PLAN for the above query tells me > >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 > > > Is there anything I can do to speed this up? > > -- > Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Still chasing "database schema has changed"
On 28 Sep 2011, at 3:01pm, Tim Streater wrote: > No, I've done no tests. I'm not concerned that each database be down to its > smallest possible size, merely that the app have a mechanism that, from time > to time, compresses certain databases through which most of the apps traffic > flows (so, plenty of rows being added and deleted). VACUUM does not do compression. What it does is recover space that is unused because you have deleted data from the database. However, so does inserting new data. So ... CREATE TABLE INSERT 1000 rows ... VACUUM here would not save any space DELETE 50 rows INSERT 50 rows ... VACUUM here would not save any space DELETE 10 rows ... VACUUM here would recover some space INSERT 10 rows ... but after this operation it is impossible to tell whether you did or didn't VACUUM. (Slightly inaccurate because some rows take up more space than other rows, but that's the idea.) So if you are constantly inserting and deleting rows, but you are inserting as much or more data than you are deleting, VACUUM does nothing to save filespace. The only time VACUUM will save space is if you've done a bunch of deleting and haven't put as much new data in since then. VACUUM does do something else: defragmentation. But since almost no operations of SQLite depend on reading many pages in sequence, defragmentation doesn't help much. I did some tests and found it difficult to see significant change except in specially designed situations which would never occur in real life. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Still chasing "database schema has changed"
On 28 Sep 2011 at 00:25, Richard Hipp wrote: > On Tue, Sep 27, 2011 at 7:16 PM, Tim Streater wrote: > >> On 27 Sep 2011 at 18:15, Richard Hipp wrote: >> >>> On Tue, Sep 27, 2011 at 1:13 PM, Tim Streater >> wrote: >>> The databases that get vacuumed tend to have a fair amount of traffic in and out. So it's good to compress them from time to time. >>> >>> Really? Have you actually measured this to see if it makes a difference? >>> What happens if you never VACUUM? >> >> They'll get bigger and bigger. I imagine the users (if I ever have any) >> would start complaining. > You know that SQLite automatically reclaims and reuses space from rows you > DELETE and tables you DROP, right? > > VACUUM repacks and defragments the database file. The repacking might make > the file a little smaller, but probably not that much. Defragmenting might > help performance, but again, probably not that much. Hence I ask: have you > actually measured the difference? No, I've done no tests. I'm not concerned that each database be down to its smallest possible size, merely that the app have a mechanism that, from time to time, compresses certain databases through which most of the apps traffic flows (so, plenty of rows being added and deleted). I'm sure I'm doing the VACUUMing unnecessarily often, but for simplicity I simply put all the timer based housekeeping in one pot. I think now I'll arrange to do that one aspect much less frequently. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] speeding up FTS4
On Sep 28, 2011, at 8:19 AM, Black, Michael (IS) wrote: > P.S. Your projects table is missing project_start. So apparently these > aren't the real create statements you are using. > > > > Sorry, I think that is the only table from which I snipped off information to make the post brief(er). These are the actual statements. The projects table is CREATE TABLE projects ( project_id INTEGER PRIMARY KEY, project_name TEXT, website_tags TEXT, twitter_tags TEXT, flickr_tags TEXT, project_start DATETIME, project_en DATETIME, project_end DATETIME ); > > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Puneet Kishor [punk.k...@gmail.com] > Sent: Tuesday, September 27, 2011 5:46 PM > To: General Discussion of SQLite Database > Subject: EXT :[sqlite] speeding up FTS4 > > I have > >CREATE TABLE uris ( >uri_id INTEGER PRIMARY KEY, >uri TEXT, >uri_content TEXT, >downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP, >feed_history_id INTEGER >); > > with 46608 rows > >CREATE TABLE feed_history ( >feed_history_id INTEGER PRIMARY KEY, >feed_id INTEGER, >scanned_on DATETIME DEFAULT CURRENT_TIMESTAMP >); > > with 3276 rows > >CREATE TABLE feeds (feed_id INTEGER PRIMARY KEY, feed_uri TEXT, project_id > INTEGER); > > with 79 rows > >CREATE TABLE projects (project_id INTEGER PRIMARY KEY, project_name TEXT); > > with 3 rows > >CREATE INDEX idx_uris_downloaded_on ON uris (downloaded_on); >CREATE VIRTUAL TABLE fts_uri USING fts4 (uri_id, uri_content); > > The database file is about 27 GB. > > > The following query takes (CPU Time: user 23.952698 sys 73.188765) returning > 46608 rows > >SELECT u.uri_id uri_id, u.uri uri, u.downloaded_on, >Snippet(fts_uri, '', '', '…', -1, > 64) snippet >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 Datetime(u.downloaded_on) >= Datetime(p.project_start) >AND fts_uri MATCH 'education,school' >ORDER BY u.uri_id, downloaded_on DESC; > > > The EXPLAIN QUERY PLAN for the above query tells me > >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 > > > Is there anything I can do to speed this up? > > -- > Puneet Kishor > ___ > 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
Re: [sqlite] SQLite 3.7.8 version - insert query speed decreased
Dear all,
I'm so sorry!
All of previous test result was my fault!
To get correct information, I'd downloaded all source code again and tested
with same configuration.
(Of course, fdatasync is enabled by using -Dfdatasync=fdatasync)
It shows almost same execution time for each version.
I think I'd made some wrong code changes. (But, cannot find the reason until
now. T.T)
Thanks a lot for your help.
And, too sorry.
2011/9/28 Richard Hipp
> Recompile with -Dfdatasync=fdatasync and rerun your test. Let us know the
> results, please.
>
> On Wed, Sep 28, 2011 at 4:31 AM, Felix.Jang wrote:
>
> > Dear Mr. Binns,
> > To test it simple and easy way, I just made test data and results on my
> > build server.
> >
> >
> > create table test (id integer primary key autoincrement, value text);
> > insert into test(value) values ("This is test data.");
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> > insert into test(value) select value from test;
> >
> > [test results]
> > ivy:~/temp$ time ./sqlite3_3763 3763.db < is.sql
> >
> > real 0m3.996s
> > user 0m3.510s
> > sys 0m0.440s
> > ivy:~/temp$ time ./sqlite3_378 378.db < is.sql
> >
> > real 0m4.622s
> > user 0m4.280s
> > sys 0m0.300s
> >
> >
> > 2011/9/28 Roger Binns
> >
> > > -BEGIN PGP SIGNED MESSAGE-
> > > Hash: SHA1
> > >
> > > On 09/27/2011 10:18 PM, YONGIL JANG wrote:
> > > > should I wait for newer version?
> > >
> > > You should provide sufficient information (ideally source code and
> data)
> > so
> > > that other people can reproduce what you are experiencing.
> > >
> > > Roger
> > > -BEGIN PGP SIGNATURE-
> > > Version: GnuPG v1.4.11 (GNU/Linux)
> > >
> > > iEYEARECAAYFAk6CuxYACgkQmOOfHg372QSymgCeO0Smredq3ZeTzdlN7u/qxIt2
> > > /AcAoKBT8CoRbSBqRpDZ1l8TE86rzldf
> > > =KlJI
> > > -END PGP SIGNATURE-
> > > ___
> > > 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
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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
Re: [sqlite] speeding up FTS4
P.S. Your projects table is missing project_start. So apparently these aren't the real create statements you are using. 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: Tuesday, September 27, 2011 5:46 PM To: General Discussion of SQLite Database Subject: EXT :[sqlite] speeding up FTS4 I have CREATE TABLE uris ( uri_id INTEGER PRIMARY KEY, uri TEXT, uri_content TEXT, downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP, feed_history_id INTEGER ); with 46608 rows CREATE TABLE feed_history ( feed_history_id INTEGER PRIMARY KEY, feed_id INTEGER, scanned_on DATETIME DEFAULT CURRENT_TIMESTAMP ); with 3276 rows CREATE TABLE feeds (feed_id INTEGER PRIMARY KEY, feed_uri TEXT, project_id INTEGER); with 79 rows CREATE TABLE projects (project_id INTEGER PRIMARY KEY, project_name TEXT); with 3 rows CREATE INDEX idx_uris_downloaded_on ON uris (downloaded_on); CREATE VIRTUAL TABLE fts_uri USING fts4 (uri_id, uri_content); The database file is about 27 GB. The following query takes (CPU Time: user 23.952698 sys 73.188765) returning 46608 rows SELECT u.uri_id uri_id, u.uri uri, u.downloaded_on, Snippet(fts_uri, '', '', '…', -1, 64) snippet 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 Datetime(u.downloaded_on) >= Datetime(p.project_start) AND fts_uri MATCH 'education,school' ORDER BY u.uri_id, downloaded_on DESC; The EXPLAIN QUERY PLAN for the above query tells me 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 Is there anything I can do to speed this up? -- Puneet Kishor ___ 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
Re: [sqlite] EXT : speeding up FTS4
Have you done "ANALYZE"? That might help. Also...try to arrange your joins based on record count (both high-to-low and low-to-high) and see what difference it makes. Since you have only one WHERE clause I'm guessing having project_ids as the first join makes sense. 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: Tuesday, September 27, 2011 5:46 PM To: General Discussion of SQLite Database Subject: EXT :[sqlite] speeding up FTS4 I have CREATE TABLE uris ( uri_id INTEGER PRIMARY KEY, uri TEXT, uri_content TEXT, downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP, feed_history_id INTEGER ); with 46608 rows CREATE TABLE feed_history ( feed_history_id INTEGER PRIMARY KEY, feed_id INTEGER, scanned_on DATETIME DEFAULT CURRENT_TIMESTAMP ); with 3276 rows CREATE TABLE feeds (feed_id INTEGER PRIMARY KEY, feed_uri TEXT, project_id INTEGER); with 79 rows CREATE TABLE projects (project_id INTEGER PRIMARY KEY, project_name TEXT); with 3 rows CREATE INDEX idx_uris_downloaded_on ON uris (downloaded_on); CREATE VIRTUAL TABLE fts_uri USING fts4 (uri_id, uri_content); The database file is about 27 GB. The following query takes (CPU Time: user 23.952698 sys 73.188765) returning 46608 rows SELECT u.uri_id uri_id, u.uri uri, u.downloaded_on, Snippet(fts_uri, '', '', '…', -1, 64) snippet 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 Datetime(u.downloaded_on) >= Datetime(p.project_start) AND fts_uri MATCH 'education,school' ORDER BY u.uri_id, downloaded_on DESC; The EXPLAIN QUERY PLAN for the above query tells me 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 Is there anything I can do to speed this up? -- Puneet Kishor ___ 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
Re: [sqlite] feed "endless" data into sqlite, thru a shell script
On 28 sept. 2011, at 13:38, Paul Linehan wrote: >> The granularity I'm looking for is between 1 second and 10 seconds. Cron is >> not >> an option here. > > I woke up this morning and there is a way that cron *_could_* do what you > want. You appear to have figured out a way that suits you, but cron could > be used. > > 10 second granularity. > > You have 6 cron jobs, each launched on the minute. > > The first launches iostat and puts data into SQLite. > The second does a sleep 10, launches iostat and puts data into SQLite, > the third sleep 20 &c. > > I know it's an appalling hack, but could be useful to somebody? That's appalling :) Especially if you consider the fact that some systems can have a crond launched with the -j flag (jitter : adds a random sleep before running cron job). patpro ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.8 version - insert query speed decreased
Recompile with -Dfdatasync=fdatasync and rerun your test. Let us know the
results, please.
On Wed, Sep 28, 2011 at 4:31 AM, Felix.Jang wrote:
> Dear Mr. Binns,
> To test it simple and easy way, I just made test data and results on my
> build server.
>
>
> create table test (id integer primary key autoincrement, value text);
> insert into test(value) values ("This is test data.");
> insert into test(value) select value from test;
> insert into test(value) select value from test;
> insert into test(value) select value from test;
> insert into test(value) select value from test;
> insert into test(value) select value from test;
> insert into test(value) select value from test;
> insert into test(value) select value from test;
> insert into test(value) select value from test;
> insert into test(value) select value from test;
> insert into test(value) select value from test;
> insert into test(value) select value from test;
> insert into test(value) select value from test;
> insert into test(value) select value from test;
> insert into test(value) select value from test;
> insert into test(value) select value from test;
> insert into test(value) select value from test;
> insert into test(value) select value from test;
> insert into test(value) select value from test;
> insert into test(value) select value from test;
> insert into test(value) select value from test;
> insert into test(value) select value from test;
>
> [test results]
> ivy:~/temp$ time ./sqlite3_3763 3763.db < is.sql
>
> real 0m3.996s
> user 0m3.510s
> sys 0m0.440s
> ivy:~/temp$ time ./sqlite3_378 378.db < is.sql
>
> real 0m4.622s
> user 0m4.280s
> sys 0m0.300s
>
>
> 2011/9/28 Roger Binns
>
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> >
> > On 09/27/2011 10:18 PM, YONGIL JANG wrote:
> > > should I wait for newer version?
> >
> > You should provide sufficient information (ideally source code and data)
> so
> > that other people can reproduce what you are experiencing.
> >
> > Roger
> > -BEGIN PGP SIGNATURE-
> > Version: GnuPG v1.4.11 (GNU/Linux)
> >
> > iEYEARECAAYFAk6CuxYACgkQmOOfHg372QSymgCeO0Smredq3ZeTzdlN7u/qxIt2
> > /AcAoKBT8CoRbSBqRpDZ1l8TE86rzldf
> > =KlJI
> > -END PGP SIGNATURE-
> > ___
> > 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
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] feed "endless" data into sqlite, thru a shell script
2011/9/27 Patrick Proniewski : >> I can't see why you would want to do this more than once every minute >> - or do you? > The granularity I'm looking for is between 1 second and 10 seconds. Cron is > not > an option here. I woke up this morning and there is a way that cron *_could_* do what you want. You appear to have figured out a way that suits you, but cron could be used. 10 second granularity. You have 6 cron jobs, each launched on the minute. The first launches iostat and puts data into SQLite. The second does a sleep 10, launches iostat and puts data into SQLite, the third sleep 20 &c. I know it's an appalling hack, but could be useful to somebody? Sincères saluations. Paul... > patpro -- lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] feed "endless" data into sqlite, thru a shell script
Am 27.09.2011 23:07, schrieb Patrick Proniewski: > On 27 sept. 2011, at 20:18, Gabor Grothendieck wrote: > >> gawk has fflush() > > > On 27 sept. 2011, at 20:29, Roger Andersson wrote: > >> stdbuf? unbuffer? > > > none of them is available out of the box on Mac OS X, or FreeBSD. > gawk can be installed, but I'd rather use my "while true" loop > instead of installing gawk. Well 'unbuffer' is a trivial Expect script, and expect IS available on OS X out of the box... http://developer.apple.com/library/mac/#documentation/Darwin/Reference/ManPages/man1/expect.1.html See for the script: http://expect.cvs.sourceforge.net/viewvc/expect/expect/example/unbuffer?revision=5.34&view=markup Michael -- Michael Schlenker Software Architect CONTACT Software GmbH Tel.: +49 (421) 20153-80 Wiener Straße 1-3 Fax:+49 (421) 20153-41 28359 Bremen http://www.contact.de/ E-Mail: m...@contact.de Sitz der Gesellschaft: Bremen Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cannot create table named "ORDER"
On Sep 28, 2011, at 12:54 PM, Se7en SenSeS wrote: > I was to create a table named ORDER to keep track of customers' orders but > there was an exception thrown enforcing me to use another name. I can have > the job done perfectly by any other DBMS like MySQL, SQL Server. Please help > me figure out whether it is a bug or not? order is a reserved word [1]. quote the name if you want to use it, e.g. "order". [1] http://www.sqlite.org/lang_keywords.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Cannot create table named "ORDER"
I was to create a table named ORDER to keep track of customers' orders but there was an exception thrown enforcing me to use another name. I can have the job done perfectly by any other DBMS like MySQL, SQL Server. Please help me figure out whether it is a bug or not? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.8 version - insert query speed decreased
Dear Mr. Binns,
To test it simple and easy way, I just made test data and results on my
build server.
create table test (id integer primary key autoincrement, value text);
insert into test(value) values ("This is test data.");
insert into test(value) select value from test;
insert into test(value) select value from test;
insert into test(value) select value from test;
insert into test(value) select value from test;
insert into test(value) select value from test;
insert into test(value) select value from test;
insert into test(value) select value from test;
insert into test(value) select value from test;
insert into test(value) select value from test;
insert into test(value) select value from test;
insert into test(value) select value from test;
insert into test(value) select value from test;
insert into test(value) select value from test;
insert into test(value) select value from test;
insert into test(value) select value from test;
insert into test(value) select value from test;
insert into test(value) select value from test;
insert into test(value) select value from test;
insert into test(value) select value from test;
insert into test(value) select value from test;
insert into test(value) select value from test;
[test results]
ivy:~/temp$ time ./sqlite3_3763 3763.db < is.sql
real 0m3.996s
user 0m3.510s
sys 0m0.440s
ivy:~/temp$ time ./sqlite3_378 378.db < is.sql
real 0m4.622s
user 0m4.280s
sys 0m0.300s
2011/9/28 Roger Binns
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 09/27/2011 10:18 PM, YONGIL JANG wrote:
> > should I wait for newer version?
>
> You should provide sufficient information (ideally source code and data) so
> that other people can reproduce what you are experiencing.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAk6CuxYACgkQmOOfHg372QSymgCeO0Smredq3ZeTzdlN7u/qxIt2
> /AcAoKBT8CoRbSBqRpDZ1l8TE86rzldf
> =KlJI
> -END PGP SIGNATURE-
> ___
> 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
