I am using the following query for searching people with lastname as thomas and firstname as haamid
"select last_name, first_name from person_profile_test where ((last_name\sc_last_name likep 'thomas*' ) and (first_name\sc_first_name\preferred_name likep 'haamid*')) and active_flag=0 order by ohr_id"
For this I am getting 995 results. Now if i change the first name to any text it still gives me the same number of results.
The results are coming only according to lastname part of AND clause "(last_name\sc_last_name likep 'thomas*' )" but the AND clause on the firstname seems to be ignored.
Hence if i search for search terms like "thomas,david" , "thomas,abd" , "thomas,haamid". it returns the same number of results.
The rank values for the above three search term comes out to be same as 372.
There are the two metamorph inverted indexes created:
You may want to use LIKE instead of LIKEP, as relevance will probably have little impact on short text such as names.
If the first LIKEP returns fewer than 1000 records (settable via maxlinearrows) then it won't use the index for other parts of the query, but should still rank. The LIKEP without an index will still return the record, but should have a zero rank for that portion of the WHERE clause.