We're running a web application based on Spring MVC (3.2.3), Hibernate (3.3.2) with Sybase ASE 15.5 as underlying database. We have several data entities (orders, participants, customers etc.) and our customer requirement is that the search should work very flexibel. This means that the user can enter one or several search terms in a search field, and that string (or number, or date) can be a partial match of any of a defined list of "searchable" fields.
Currently, this results in ugly search SQL in the form of
select ...
from ORDERS ordermodel0_
inner join ADDRESSMASTER addressmas1_ on ordermodel0_.OWNERID=addressmas1_.ID
where (ordermodel0_.TITLE like '%smith%'
or ordermodel0_.EXTERNALNR like '%smith%'
or addressmas1_.NAME like '%smith%'
or addressmas1_.FIRSTNAME like '%smith%'
or exists (select participan2_.ID from PARTICIPANTS participan2_
where ordermodel0_.ID=participan2_.ORDERID
and (participan2_.FIRSTNAME like '%smith%'
or participan2_.LASTNAME like '%smith%')))
Ultimately, this results in a query plan reading all of those tables (table scan), as the wildcard search doesn't allow for using any index. We have 500k rows on ORDERS, 1.2 Mio rows on PARTICIPANTS and 500k rows on ADDRESSMASTER. With so little data, search results should be available almost instantaneously, but they are not. Searches can take up to several seconds, depending on the number of lock waits.
Would a full-text search resolve this issue? Does anyone have experience with a similar situation that has already been resolved? Sybase used to offer a "ASE - Enhanced Full Text Search Option", but that was discontinued without replacement. Should we look into Apache Lucene?
Thanks Simon
