• Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry

Comments (2)

1 Mark_Ashworth commented Permalink

I had a question on relating to the first example using the index:

 
create index bts_idx on address(
fname bts_char_ops,
lname bts_char_ops,
address1 bts_varchar_ops,
address2 bts_varchar_ops,
city bts_char_ops,
province bts_char_ops,
country bts_char_ops,
postalcode bts_char_ops) using bts;
 
If there are rows that share the lname value but different fname values, for example:
 
fname = mark, lname = ashworth
fname = nancy, lname = ashworth
fname = jim, lname = ashworth
fname = david, lname = ashworth
 
When we query this table with the following select:
 
select * from address where bts_contains (first, 'mark ashworth');
 
The query returns all 4 rows with the last name ashworth. The reason is because the query parse string 'mark ashworth' has an implicit OR, ie it is equivalent to 'mark OR ashworth'. If you wanted just rows with the words mark and ashworth, you need to specify the AND explicitly:
 
select * from address where bts_contains (first, 'mark AND ashworth');
 
However, it would also find rows where mark appears in one of the other columns in the composite index. For that, you need to create an index as I describe in the second part of this blog entry.

2 KevinsUniqueName commented Permalink

Hi Mark,

 
Is it possible to search across all fields in the lucene document using bts_contains?
 
It looks like that's what you're specifying as the default field, but since bts_contains takes a column name as an argument, it doesn't look possible.
 
thanks,
Kevin
 

Add a Comment Add a Comment