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.000099 sys 0.000008
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
