On Jun 17, 2004, at 10:15 PM, Gary Jarrel wrote:
> The "componentIDList" is actually retrieved using Lucene which indexes
> the
> records in the database.
...
> Doing such a search using LIKE expressions in WHERE clause in SQL
> took way too
> much time. Lucene has so far proven to be the best solution for
> searching.
That's right, Lucene was mentioned in the code comments... I am
definitely not suggesting to switch away from Lucene, I just totally
overlooked that you were using it.
Ok, another idea...
[Disclaimer: I never used any of the stuff below on a real project,
just did some preliminary research in the past. If anyone has better
experience, please correct me]
Most major databases (excluding Postgres I think) have some sort of
proprietary "free text search" SQL facility that does the same thing as
Lucene, but is actually handled inside DB, so you can join the results
any way you want. It is called different names on different platforms,
but the idea is the same. When investigating a free text search
facility, I remember that SQLServer implementation struck me as the
most advanced, compared to others like Oracle, MySQL, etc.
The drawbacks that I see are the following:
1. Maintaining a free text search on the DB side seems to require a
fair amount of special maintenance (but so does probably the Lucene
index).
2. SQL to do the search is proprietary (might be an area where Cayenne
might shine if we implement a cross-DB abstract query).
Andrus
This archive was generated by hypermail 2.0.0 : Thu Jun 17 2004 - 22:44:56 EDT