http://www.perlmonks.org?node_id=1011756


in reply to Re^2: Writing a database lookup tool
in thread Writing a database lookup tool

I don't think you will be limited by Sqlite's database size capabilities - see my reply to marto above.

FYI - current versions include FTS (Full text search) capabilities.

Sqlite and perl run on every platform I have heard of.

I'm not familiar with Solr - it certainly looks interesting - and may map better to your problem domain. However, I got the impression you wanted a stand-alone (not web-server based) solution. Sqlite may be better for that option. Yes - you will need to install each stand-alone instance, but installation is trivial - just copy the file in. Also - it is possible for multiple sqlite clients to access a single shared sqlite database file.

             "By three methods we may learn wisdom: First, by reflection, which is noblest; Second, by imitation, which is easiest; and third by experience, which is the bitterest."           -Confucius

Replies are listed 'Best First'.
Re^4: Writing a database lookup tool
by elef (Friar) on Jan 18, 2013 at 11:54 UTC
    "FYI - current versions include FTS (Full text search) capabilities."
    Thank you for that info. Full text search looks like it was designed for precisely the type of queries I'd be using. Based on the descriptions I found, it would add a lot of functionality and a lot of speed. Now, the main question is: do I get FTS with the perl database modules? The DBI::DB and DBD::SQLite cpan pages don't mention FTS, but it seems to be a pretty old feature so it should have trickled down, right?

    Edit: I digged around a bit more and found out that FTS is supported by DBD::SQLite: http://blogs.perl.org/users/adam_kennedy/2012/05/next-dbdsqlite-to-be-released-in-early-june.html

    "However, I got the impression you wanted a stand-alone (not web-server based) solution."
    Yes. The database and the lookup tool would be on the "client" machine, and ideally the whole thing should be reasonably compact and self-contained. One not too large (hopefully <50MB without the data) download, one not too complex installation.
      Update: I started playing with DBD::SQLite. I imported 800,000 records into a db, and quickly realized that by default, most of my searches are indeed carried out as sequential searches, which makes them hopelessly slow. With FTS enabled, average lookup times fell from 4 seconds to 0.04 seconds. If this scales in a roughly linear fashion and I get 0.5 sec lookups on 10 million records, I'll be very happy with the speed.
      Thanks again.