On 01/29/2015 02:29 AM, farkas andras wrote:
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?

Looks like range constraints on rowids were only taken into account when there was also a MATCH term in the WHERE clause. Now fixed here:


The fix should be in 3.8.9.


sqlite-users mailing list

Reply via email to