Hi all, I'm using FTS through DBD::SQLite (perl) to query large text
databases (~10GB, ~10 million records). The regular FTS MATCH searches work
fine (they usually run under a second), but searches based on ROWID are
atrociously slow and hog massive amounts of memory. I'm trying to retrieve
a couple of adjacent rows like so:
my $q_c = $dbh->prepare( "SELECT * FROM ftstable WHERE (ROWID BETWEEN 1000 AND
1040)" );
# my $q_c = $dbh->prepare( "SELECT * FROM ftstable LIMIT 1040 OFFSET 1000" ); #
tried this too, it isn't any better
$q_c->execute();
The execute takes several minutes and uses ~600 MB of memory. Now,
http://www.sqlite.org/changes.html writes that:
3.8.1: FTS4 queries are better able to make use of docid<$limit constraints to
limit the amount of I/O required
There's also this thread, indicating that rowid searches on FTS databases
are optimized:
http://sqlite.1065341.n5.nabble.com/FTS-full-text-query-vs-query-by-rowid-td77534.html
I was using 3.7.x so I updated DBD::SQLite and that got me up to SQLite 3.8.7,
but I see no significant improvement. Explain query gives the same result as
the linked thread: 0|0|0|SCAN TABLE tmdata VIRTUAL TABLE INDEX 393216.
Maybe there is a better way to write the query? If not, is there any hope that
this will get fixed? Thanks,András Farkas
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
