bioperl/bioperl-live — pull request #91 by nathanweeks (2014-11-14 21:59:26+00:00)
Title: Support FTS attribute table and WITHOUT ROWID optimization for Bio::DB::SeqFeature::Store::DBI::SQLite

Several of our GBrowse instances had issues with full-text (attribute) searches timing out. Profiling revealed that the execution time of Bio::DB::SeqFeature::Store::search_attributes on our Bio::DB::SeqFeature::Store::DBI::SQLite databases contributed significantly to this problem. The proposed changes, which add support for indexing the attribute table using SQLite's FTS (full-text search) extension, resolved the issue (when used in conjunction with Scott Cain's recent commit that removed the use of CGI::Pretty in GBrowse: https://github.com/GMOD/GBrowse/commit/298cab3ece8f68b7baf2e9d085fab9055772fa3c)

To demonstrate the performance impact of an FTS attribute table on search_attributes(), consider the following script:

```
# search_attributes.pl
use strict; use warnings;
use Bio::DB::SeqFeature::Store;

my $db = Bio::DB::SeqFeature::Store->new(-adaptor => 'DBI::SQLite',
-dsn => $ARGV[0]);

my @features = $db->search_attributes($ARGV[1],
['arabidopsis_defline', 'arabidopsis_symbol', 'pfam', 'go', 'panther',
'kegg_enzyme', 'kegg_orthology', 'cog_cluster']);

print 'Features: ' . scalar(@features) . "\n";
```

Given a Bio::DB::SeqFeature::Store::DBI::SQLite database with gene models & annotation created from a GFF3 file with 692,300 features, the following were typical observed execution times in our environment:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$ time perl search_attributes.pl track-orig.db iron
Features: 1283

real 0m1.61s
user 0m0.71s
sys 0m0.89s
$ time perl search_attributes.pl track-fts.db iron
Features: 1280

real 0m0.27s
user 0m0.20s
sys 0m0.06s
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The difference in number of matches is due to the difference in behavior between the two methods: FTS (with the MATCH operator) searches for tokens, while LIKE '%iron%' finds substrings. The extra three results returned with "LIKE '%iron%'" contained a spurious match containing "iron" as a substring:

acclimation of photosynthesis to environment

and two occurrences of "diiron", which may be relevant to a user:

dicarboxylate diiron protein, putative (Crd1)

OTOH, if the user searches for "Fe" instead, they get "real" hits with an FTS attribute table, whereas the non-FTS search returns thousands of spurious hits where "fe" is a substring.

Because of this difference in behavior (and possible portability issues to systems with old DBD::SQLite instances---see below), I thought that FTS should be op-in rather than the default.

Also, note that FTS support depends on the version of DBD::SQLite. The current DBD::SQLite by default supports two versions: FTS3 since sometime before 1.30_04 (2010-08-25), and FTS4 since 1.36_01 (2012-01-19).

At least one design decision I made while implementing this change should be considered/debated before accepting this pull request: the -fts option is just a boolean flag. My initial implementation supported the creation of an FTS attribute table using a user-specified FTS version, but at the last minute I decided to KISS and just use the most recent version supported by the installed DBD::SQLite. This isn't a problem if someone decides to implement an FTS attribute table for MySQL, which supports only one such index type (FULLTEXT: http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html). However, it's conceivable that one might want to implement FTS for PostgreSQL and have control over whether GIN or GiST indexing is used (http://www.postgresql.org/docs/9.3/static/textsearch-indexes.html), or, with SQLite, specify FTS3 at database (instead of the most recent FTS4) at creation time to allow its use on a host with an older DBD::SQLite.