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, '<span class="hilite">', '</span>', '…', -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
