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
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&#39;s also this thread, indicating that rowid searches on FTS databases 
are optimized: 
 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

Reply via email to