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;
>
>
> ..
<snipped a bunch of stuff>
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, '<span class="hilite">', '</span>',
'…', -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.000086 sys 0.000006
and yet
sqlite> SELECT u.uri_id uri_id, u.uri uri, u.u_downloaded_on,
...> Snippet(fts_uri, '<span class="hilite">', '</span>',
'…', -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
