I would suggest you try LuSql, which was designed specifically to index relational databases into Lucene.
It has an extensive user manual/tutorial which has some complex examples involving multi-joins and sub-queries. I am the author of LuSql. LuSql home page: http://lab.cisti-icist.nrc-cnrc.gc.ca/cistilabswiki/index.php/LuSql LuSql manual: http://cuvier.cisti.nrc.ca/~gnewton/lusql/v0.9/lusqlManual.pdf.html thanks, Glen 2009/2/28 <spr...@gmx.eu>: > Hi, > > what is the best approach to merge a database index with a lucene fulltext > index? Both databases store a unique ID per doc. This is the join criteria. > > requirements: > > * both resultsets may be very big (100.000 and much more) > * the merged resultset must be sorted by database index and/or relevance > * optional paging the merged resultset, a page has a size of 1000 docs max. > > example: > > select a, b from dbtable where c = 'foo' and content='bar' order by > relevance, a desc, d > > I would split this into: > > database: select ID, a, b from dbtable where c = 'foo' order by a desc, d > lucene: content:bar (sort:relevance) > merge: loop over the lucene resultset and add the db record into a new list > if the ID matches. > > If the resultset must be paged: > > database: select ID from dbtable where c = 'foo' order by a desc, d > lucene: content:bar (sort:relevance) > merge: loop over the lucene resultset and add the db record into a new list > if the ID matches. > page 1: select a,b from dbtable where ID IN (list of the ID's of page 1) > page 2: select a,b from dbtable where ID IN (list of the ID's of page 2) > ... > > > Is there a better way? > > Thank you. > > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: java-user-unsubscr...@lucene.apache.org > For additional commands, e-mail: java-user-h...@lucene.apache.org > > -- - --------------------------------------------------------------------- To unsubscribe, e-mail: java-user-unsubscr...@lucene.apache.org For additional commands, e-mail: java-user-h...@lucene.apache.org
