You are viewing a plain text version of this content. The canonical link for it is here.
Posted to java-user@lucene.apache.org by Glen Stampoultzis <gs...@iinet.net.au> on 2004/05/11 05:02:08 UTC
Mixing database and lucene searches
Hi... I have a query screen where most of the fields search a regular
database but one field searches for text in the body of the document. You
could say the database holds metadata about the documents. Effectively this
means I have two separate queries going on - a lucene query and a database
query. These results need to be combined.
Anyone have any strategies for dealing with this? I'm wondering whether
it's better to replicate searchable fields in the lucene index. This means
being very careful that updates get done in two places so it is not ideal.
Regards,
Glen
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Mixing database and lucene searches
Posted by Paul <pa...@waite.net.nz>.
Glen Stampoultzis wrote:
> Hi... I have a query screen where most of the fields search a regular
> database but one field searches for text in the body of the document. You
> could say the database holds metadata about the documents. Effectively
> this means I have two separate queries going on - a lucene query and a
> database query. These results need to be combined.
>
> Anyone have any strategies for dealing with this? I'm wondering whether
> it's better to replicate searchable fields in the lucene index. This means
> being very careful that updates get done in two places so it is not ideal.
First index your docs with all fields that are queried on, and storing the
db key.For each query check the terms to see if the any non-metadata (Lucene)
query data has been submitted, If so, run a Lucene query, and build a
resultset of keys. If not, go straight to the database and do the same with a
standard db query.
The Lucene returns are then filled in with a database lookup for each doc
in the returned resultset to provide metadata.
Cheers,
Paul.
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
RE: index on SAN (storage area network)
Posted by hui <hu...@triplehop.com>.
Thank you very much for the suggestion, Otis.
I did write a test program to check whether the directory is a directory.
But I did not test it within the web application. It is related with tomcat
security or the SAN setup. I need to figure out.
Regards,
Hui
-----Original Message-----
From: Otis Gospodnetic [mailto:otis_gospodnetic@yahoo.com]
Sent: Thursday, May 13, 2004 6:51 AM
To: Lucene Users List
Subject: Re: index on SAN (storage area network)
My guess would be that you just have a wrong path to the index
directory somewhere. You can add your own checks to your own code,
before you create IndexSearcher/Reader.
Something like:
if (new File(/path/to/your/index/dir).isDirectory()) - ok, else not ok
;)
Otis
--- hui <hu...@triplehop.com> wrote:
> Hi,
> I am getting "not a directory" error when doing search after I moved
> the
> index from local to a SAN box. FSDirectory does not recognize the
> index
> directory as a directory. Any idea? I use JDK142 and Lucene 1.3
> final.
>
> Creating the index works fine.
>
> Regards,
> hui
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: lucene-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: index on SAN (storage area network)
Posted by Otis Gospodnetic <ot...@yahoo.com>.
My guess would be that you just have a wrong path to the index
directory somewhere. You can add your own checks to your own code,
before you create IndexSearcher/Reader.
Something like:
if (new File(/path/to/your/index/dir).isDirectory()) - ok, else not ok
;)
Otis
--- hui <hu...@triplehop.com> wrote:
> Hi,
> I am getting "not a directory" error when doing search after I moved
> the
> index from local to a SAN box. FSDirectory does not recognize the
> index
> directory as a directory. Any idea? I use JDK142 and Lucene 1.3
> final.
>
> Creating the index works fine.
>
> Regards,
> hui
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: lucene-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
index on SAN (storage area network)
Posted by hui <hu...@triplehop.com>.
Hi,
I am getting "not a directory" error when doing search after I moved the
index from local to a SAN box. FSDirectory does not recognize the index
directory as a directory. Any idea? I use JDK142 and Lucene 1.3 final.
Creating the index works fine.
Regards,
hui
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Mixing database and lucene searches
Posted by Erik Hatcher <er...@ehatchersolutions.com>.
On May 10, 2004, at 11:02 PM, Glen Stampoultzis wrote:
> Hi... I have a query screen where most of the fields search a regular
> database but one field searches for text in the body of the document.
> You
> could say the database holds metadata about the documents.
> Effectively this
> means I have two separate queries going on - a lucene query and a
> database
> query. These results need to be combined.
>
> Anyone have any strategies for dealing with this? I'm wondering
> whether
> it's better to replicate searchable fields in the lucene index. This
> means
> being very careful that updates get done in two places so it is not
> ideal.
Beyond what the other responses on this thread have been, another
option is to use a custom Filter which can narrow a Lucene query down
based on external criteria. Wrap that with the caching filter wrapper
if things don't change and this could be highly efficient for
successive searches.
In CVS HEAD, there is a new FilteredQuery which could leverage any
custom Filter you create and allow you to constrain a query clause by
DB criteria.
I built a proof-of-concept one for the book, which looks like this:
public interface SpecialsAccessor {
String[] isbns();
}
public class SpecialsFilter extends Filter {
private SpecialsAccessor accessor;
public SpecialsFilter(SpecialsAccessor accessor) {
this.accessor = accessor;
}
public BitSet bits(IndexReader reader) throws IOException {
BitSet bits = new BitSet(reader.maxDoc());
String[] isbns = accessor.isbns();
int[] docs = new int[1];
int[] freqs = new int[1];
for (int i = 0; i < isbns.length; i++) {
String isbn = isbns[i];
if (isbn != null) {
TermDocs termDocs =
reader.termDocs(new Term("isbn", isbn));
int count = termDocs.read(docs, freqs);
if (count == 1) {
bits.set(docs[0]);
}
}
}
return bits;
}
public String toString() {
return "SpecialsFilter";
}
}
The implementation of SpecialsAccessor is left as an exercise for the
reader :) (I implemented a Mock version as an example). Consider
adapting something like this for your DB access, then wrap with the
caching wrapper.
Erik
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Mixing database and lucene searches
Posted by Paul <pa...@waite.net.nz>.
Erik Hatcher wrote:
> On May 11, 2004, at 7:41 PM, Glen Stampoultzis wrote:
> > Is it possible to use float and date ranges in that case? Or maybe I
> > should
> > just read the details in the manual and stop asking stupid questions.
> >
> > :-)
>
> Terms in the index are *always* text. The DateField class in Lucene
> helps do conversions, but see the wiki for more info on date fields. I
> wouldn't recommend using it, personally, unless millisecond granularity
> is really desired.
For date handling and querying we munge the datetime in seconds to be
a lexicographically ordered value, but the standard YYYYMMDDHHMMSS
string format is just as good.
Because in our app people are generally interested in day granularity rather
than seconds for date-range querying, we index on a date value and use
that field for queries. However for subsequent sorting we have a full datetime
value which is a stored Lucene field.
Since we index about 1,000 articles per day, this means the boolean term
expansion, based on the date, is at an acceptable level (in the 10's usually)
whereas trying to utilize the datetime field for that would generally require
1,000x more and cause resource problems.
Cheers,
Paul.
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Mixing database and lucene searches
Posted by Erik Hatcher <er...@ehatchersolutions.com>.
On May 11, 2004, at 8:33 PM, Matt Quail wrote:
> For float, you may have to pick some range of floats that you will
> allow, and use a java.util.DecimalFormat to format them to a
> fixed-width, zero-padded string. If you are going to allow -ve values,
> then it gets trickier (I have the trick if you need to know this).
Care to post it here:
http://wiki.apache.org/jakarta-lucene/SearchNumericalFields
??
Thanks,
Erik
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Mixing database and lucene searches
Posted by Matt Quail <ma...@ctx.com.au>.
> Is it possible to use float and date ranges in that case? Or maybe I should
> just read the details in the manual and stop asking stupid questions. :-)
There is no such thing as a stupid question ;-)
At the end of the day, Lucene just handles strings; and it handles them
lexocographically. The DateField class turns a Date into a string that
sorts well. (DateField doesn't handle all values of a 'long', though).
For float, you may have to pick some range of floats that you will
allow, and use a java.util.DecimalFormat to format them to a
fixed-width, zero-padded string. If you are going to allow -ve values,
then it gets trickier (I have the trick if you need to know this).
=Matt
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Mixing database and lucene searches
Posted by Erik Hatcher <er...@ehatchersolutions.com>.
On May 11, 2004, at 7:41 PM, Glen Stampoultzis wrote:
>
> Is it possible to use float and date ranges in that case? Or maybe I
> should
> just read the details in the manual and stop asking stupid questions.
> :-)
Terms in the index are *always* text. The DateField class in Lucene
helps do conversions, but see the wiki for more info on date fields. I
wouldn't recommend using it, personally, unless millisecond granularity
is really desired.
For other types, if you need range queries, you need to make the
textual representation be ordered lexicographically (i.e. pad the
numbers with leading zeros so that all numbers have the same width) -
again, some of this is on the wiki.
There really isn't anything in the "manual" about field types, that I
know of, only brief mentions on the wiki and e-mail archives.
Erik
>
> "Matt Quail" <ma...@ctx.com.au> wrote in message
> news:40A15BF3.6050301@ctx.com.au...
>> Eric Jain wrote:
>>>> To ask a silly question: What approach does Lucene use for
>>>> ranges and sorting?
>>>
>>>
>>> A range such as '10-60' is expanded into a boolean query containing
>>> all
>>> terms that are in the index and lie within the specified range, e.g.
>>> '10
>>> or 11 or 20 or 59'.
>>
>> Yes, using a range search requires loading all the (existant) terms
>> between '10' and '60' and combining them into one big Boolean "or"
>> query. There is a limit to how big such a query can get.
>>
>> The performant alternative is to use a Filter. You lose the ability to
>> do boosting on that field, but it can handle ranges of any size.
>>
>> =Matt
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: lucene-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Mixing database and lucene searches
Posted by Glen Stampoultzis <gs...@iinet.net.au>.
Is it possible to use float and date ranges in that case? Or maybe I should
just read the details in the manual and stop asking stupid questions. :-)
"Matt Quail" <ma...@ctx.com.au> wrote in message
news:40A15BF3.6050301@ctx.com.au...
> Eric Jain wrote:
> >>To ask a silly question: What approach does Lucene use for
> >>ranges and sorting?
> >
> >
> > A range such as '10-60' is expanded into a boolean query containing all
> > terms that are in the index and lie within the specified range, e.g. '10
> > or 11 or 20 or 59'.
>
> Yes, using a range search requires loading all the (existant) terms
> between '10' and '60' and combining them into one big Boolean "or"
> query. There is a limit to how big such a query can get.
>
> The performant alternative is to use a Filter. You lose the ability to
> do boosting on that field, but it can handle ranges of any size.
>
> =Matt
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Mixing database and lucene searches
Posted by Matt Quail <ma...@ctx.com.au>.
Eric Jain wrote:
>>To ask a silly question: What approach does Lucene use for
>>ranges and sorting?
>
>
> A range such as '10-60' is expanded into a boolean query containing all
> terms that are in the index and lie within the specified range, e.g. '10
> or 11 or 20 or 59'.
Yes, using a range search requires loading all the (existant) terms
between '10' and '60' and combining them into one big Boolean "or"
query. There is a limit to how big such a query can get.
The performant alternative is to use a Filter. You lose the ability to
do boosting on that field, but it can handle ranges of any size.
=Matt
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Mixing database and lucene searches
Posted by Eric Jain <Er...@isb-sib.ch>.
> To ask a silly question: What approach does Lucene use for
> ranges and sorting?
A range such as '10-60' is expanded into a boolean query containing all
terms that are in the index and lie within the specified range, e.g. '10
or 11 or 20 or 59'.
For sorting, Lucene loads into memory all terms that occur in a field
according to which someone may want to sort. Can't give you any further
information, as that's where I stopped reading :-)
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Mixing database and lucene searches
Posted by Glen Stampoultzis <gs...@iinet.net.au>.
"Eric Jain" <Er...@isb-sib.ch> wrote in message
news:007e01c43734$98fe3480$c300000a@caliente...
> > If you *really* don't want to (or can't) put all the searchable fields
> > into lucene, then you are going to need to do a "lucene-db" join.
>
> Here are two good reasons:
>
> 1. Range queries
> 2. Sorting
>
> Yes, Lucene can do both, but I find that in both cases the approach
> Lucene uses is not suitable for large data sets, given limited hardware
> resources.
To ask a silly question: What approach does Lucene use for ranges and
sorting?
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Mixing database and lucene searches
Posted by Glen Stampoultzis <gs...@iinet.net.au>.
"Eric Jain" <Er...@isb-sib.ch> wrote in message
news:007e01c43734$98fe3480$c300000a@caliente...
> > If you *really* don't want to (or can't) put all the searchable fields
> > into lucene, then you are going to need to do a "lucene-db" join.
>
> Here are two good reasons:
>
> 1. Range queries
> 2. Sorting
>
> Yes, Lucene can do both, but I find that in both cases the approach
> Lucene uses is not suitable for large data sets, given limited hardware
> resources.
>
>
> > Hits hits = searcher.search(new TermQuery("text", "foo")
> > Set hitPKs = new Set();
> > for each doc in hits:
> > hitPKs.put(doc.getField("pk"))
>
> Retrieving even one custom field for every document of a possibly large
> data set
> can end up being very slow, it seems. This complicates things a lot...
>
> Unfortunately, I am not aware of any good solutions for combining Lucene
> with a relational database, given the requirements listed above.
> However, one promising approach may involve combing Lucene with the new
> Berkely DB JE:
>
> 1. Use Lucene to create a bitset of results (position = docid).
> 2. Use BDB to iterate through primary keys, sorted and restricted by one
> (or more?) of several criteria.
> 3. For each primary key, look up docid (this database must be rebuilt
> every time the index is modified).
> 4. If docid set in result bitset, report result.
>
> If anyone has tried anything similar, I'd be interested to know!
Why Berkely DB? This sounds like it would work regardless of the database.
Regards,
Glen
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Mixing database and lucene searches
Posted by Eric Jain <Er...@isb-sib.ch>.
> If you *really* don't want to (or can't) put all the searchable fields
> into lucene, then you are going to need to do a "lucene-db" join.
Here are two good reasons:
1. Range queries
2. Sorting
Yes, Lucene can do both, but I find that in both cases the approach
Lucene uses is not suitable for large data sets, given limited hardware
resources.
> Hits hits = searcher.search(new TermQuery("text", "foo")
> Set hitPKs = new Set();
> for each doc in hits:
> hitPKs.put(doc.getField("pk"))
Retrieving even one custom field for every document of a possibly large
data set
can end up being very slow, it seems. This complicates things a lot...
Unfortunately, I am not aware of any good solutions for combining Lucene
with a relational database, given the requirements listed above.
However, one promising approach may involve combing Lucene with the new
Berkely DB JE:
1. Use Lucene to create a bitset of results (position = docid).
2. Use BDB to iterate through primary keys, sorted and restricted by one
(or more?) of several criteria.
3. For each primary key, look up docid (this database must be rebuilt
every time the index is modified).
4. If docid set in result bitset, report result.
If anyone has tried anything similar, I'd be interested to know!
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Mixing database and lucene searches
Posted by Paul <pa...@waite.net.nz>.
Matt Quail wrote:
> > But presumably if you make sure that the field "name" is indexed in
> > Lucene (not necessarily stored), it's far quicker to do a two-stage
> > process, where you search Lucene for "text:foo AND name:matt" and then
> > just fill-in any other metadata with a fast little DB lookup for the
> > small returned set of primary keys?
>
> Totally, but Glen's original question was one where some of the fields
>
> were *not* in the index:
> > I have a query screen where most of the fields search a regular
> > database but one field searches for text in the body of the document.
Ah I see. I was thinking he was flexible about how he was going to
set everything up..
Cheers,
Paul.
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Mixing database and lucene searches
Posted by Matt Quail <ma...@ctx.com.au>.
> But presumably if you make sure that the field "name" is indexed in
> Lucene (not necessarily stored), it's far quicker to do a two-stage process,
> where you search Lucene for "text:foo AND name:matt" and then just fill-in
> any other metadata with a fast little DB lookup for the small returned set of
> primary keys?
Totally, but Glen's original question was one where some of the fields
were *not* in the index:
> I have a query screen where most of the fields search a regular
> database but one field searches for text in the body of the document.
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Mixing database and lucene searches
Posted by Paul <pa...@waite.net.nz>.
Matt Quail wrote:
> "And" join: if you want to do a query like "select rows where
> name='matt' and text contains 'foo'", then the psuedo code is:
> Hits hits = searcher.search(new TermQuery("text", "foo")
> Set hitPKs = new Set();
> for each doc in hits:
> hitPKs.put(doc.getField("pk"))
>
> rsPKs = new Set();
> ResultSet rs = sql.execute("select PK from TABLE where name='matt'")
> for each row in rs:
> rsPKs.put(rs.get("pk"));
> Set results = intersection(rsPKs, hitPKs);
But presumably if you make sure that the field "name" is indexed in
Lucene (not necessarily stored), it's far quicker to do a two-stage process,
where you search Lucene for "text:foo AND name:matt" and then just fill-in
any other metadata with a fast little DB lookup for the small returned set of
primary keys?
Cheers,
Paul.
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Mixing database and lucene searches
Posted by Matt Quail <ma...@ctx.com.au>.
Glen Stampoultzis wrote:
> Just one comment about your strategy for combining db and lucene searches.
> It seems that it would slow down significantly the larger the results,
> although I can't see a better way to go about it. For example if the lucene
> search matched 100 records and the database searched 1,000,000 records
> you're faced with iterating through a set of 1,000,000 records for a result
> that couldn't possibly be more than 100 actual records.
Yeah, I know. It totally sucks. Putting all your fields into the index
is the best thing... *and* it makes your searches way faster at the same
time.
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Mixing database and lucene searches
Posted by Glen Stampoultzis <gs...@iinet.net.au>.
Thanks for the help.
I think I'll got with putting all the fields into lucene.
Just one comment about your strategy for combining db and lucene searches.
It seems that it would slow down significantly the larger the results,
although I can't see a better way to go about it. For example if the lucene
search matched 100 records and the database searched 1,000,000 records
you're faced with iterating through a set of 1,000,000 records for a result
that couldn't possibly be more than 100 actual records. I guess there's not
really much you can do about that.
Regards,
Glen
"Matt Quail" <ma...@ctx.com.au> wrote in message
news:40A04C41.1070400@ctx.com.au...
> Glen Stampoultzis wrote:
> > Anyone have any strategies for dealing with this? I'm wondering whether
> > it's better to replicate searchable fields in the lucene index. This
means
> > being very careful that updates get done in two places so it is not
ideal.
>
> If you *can* manage to update your index when the "real" database is
> updaed, then indexing the database fields into the lucene index is the
> way to go: because it makes searching easier (no lucene-db joins) and
> faster (way faster!).
>
> You could always "batch" the updates of the index. That is, if you can
> put a "last modified" date on each row of the database, then updating
> the lucene index is easy (the index just needs to remember the last date
> at which it was updated).
>
> If you *really* don't want to (or can't) put all the searchable fields
> into lucene, then you are going to need to do a "lucene-db" join. This
> is how I do it:
>
> Just say your DB contains rows with a primary key "pk" (say, an int;
> this is a Keyword in the lucene index), a field "author" (not indexed)
> and a text blob "text" (this is what you have indexed in Lucene).
>
> There are two basic joins you need to do, an "and" join and an "or" join.
>
> "And" join: if you want to do a query like "select rows where
> name='matt' and text contains 'foo'", then the psuedo code is:
>
> ---
> Hits hits = searcher.search(new TermQuery("text", "foo")
> Set hitPKs = new Set();
> for each doc in hits:
> hitPKs.put(doc.getField("pk"))
>
> rsPKs = new Set();
> ResultSet rs = sql.execute("select PK from TABLE where name='matt'")
> for each row in rs:
> rsPKs.put(rs.get("pk"));
>
> Set results = intersection(rsPKs, hitPKs);
> ---
>
> Similarly, the "or" query uses union() not intersection(). If you want
> to preserve the order of the Hits, then use a List or LinkedSet instead.
>
> The code for a lucene-db is very annoying: try to put all your
> searchable fields into an Index instead :D
>
> =Matt
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org
Re: Mixing database and lucene searches
Posted by Matt Quail <ma...@ctx.com.au>.
Glen Stampoultzis wrote:
> Anyone have any strategies for dealing with this? I'm wondering whether
> it's better to replicate searchable fields in the lucene index. This means
> being very careful that updates get done in two places so it is not ideal.
If you *can* manage to update your index when the "real" database is
updaed, then indexing the database fields into the lucene index is the
way to go: because it makes searching easier (no lucene-db joins) and
faster (way faster!).
You could always "batch" the updates of the index. That is, if you can
put a "last modified" date on each row of the database, then updating
the lucene index is easy (the index just needs to remember the last date
at which it was updated).
If you *really* don't want to (or can't) put all the searchable fields
into lucene, then you are going to need to do a "lucene-db" join. This
is how I do it:
Just say your DB contains rows with a primary key "pk" (say, an int;
this is a Keyword in the lucene index), a field "author" (not indexed)
and a text blob "text" (this is what you have indexed in Lucene).
There are two basic joins you need to do, an "and" join and an "or" join.
"And" join: if you want to do a query like "select rows where
name='matt' and text contains 'foo'", then the psuedo code is:
---
Hits hits = searcher.search(new TermQuery("text", "foo")
Set hitPKs = new Set();
for each doc in hits:
hitPKs.put(doc.getField("pk"))
rsPKs = new Set();
ResultSet rs = sql.execute("select PK from TABLE where name='matt'")
for each row in rs:
rsPKs.put(rs.get("pk"));
Set results = intersection(rsPKs, hitPKs);
---
Similarly, the "or" query uses union() not intersection(). If you want
to preserve the order of the Hits, then use a List or LinkedSet instead.
The code for a lucene-db is very annoying: try to put all your
searchable fields into an Index instead :D
=Matt
---------------------------------------------------------------------
To unsubscribe, e-mail: lucene-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: lucene-user-help@jakarta.apache.org