[Date Prev][Date Next] [Chronological] [Thread] [Top]

Will lmdb suit my needs ?

Hi,

I have been using sqlite3 as the database for my application. My
application has three columns and about 10 million rows.

The query that I do is:

SELECT DISTINCT column2 FROM autocomplete WHERE column1 LIKE
'<prefix>%' ORDER BY column3 DESC LIMIT 5

where column1 and column2 are strings and column3 is int.

So, what my query essentially does is get a prefix string from user
and get the top (sorted by column3) 5 records where column1 start with
the input prefix and return the column2 of those records.

In my sqlite, the first time query for each key (a or b or c ...) is
taking about 350 seconds (with an index on all columns) and about 500
seconds (with an index on only column1).

The file size is about 800 MB for 1-column index and about 1.1 GB for
the sqlite db with an all column index.

Subsequent queries to the database with a pre-loaded prefix chars
(i.e., any second call with a search key a or b or c), return in
milliseconds.

My database will be constructed (written) only once and will be just
accessed (read) then after. There may not even be any updates/writes
after the construction.

I could not find any tutorial for lmdb by googling. I would prefer to
see some simple documentation that explains at least the basic set of
APIs that we need to use (say the lmdb equivalents of sqlite3_open,
sqlite3_exec, sqlite3_close)

Also, since I do not have much exposure to key-value dbs, will it be
possible to provide a custom compartor function for values (so that I
can store column2+column3 as a value and let the comparator function
use column3 only as a value compartor function) ?

Also one humble suggestion is that it may be better if the lmdb
sources could be moved to a separate git repo instead of as a branch
on the openldap git. That way it will be easier for potential
consumers of this library to easily google it and access/build the
sources.

Will lmdb be suitable for my needs ? And can someone point to any
tutorial ? Thanks.

-- 
Sankar P
http://psankar.blogspot.com