fulltext search headnut

[BadDotNetCoda]

hi,
since a few days i try to develop a fulltextsearch for adresses.
ive a normalized adress table with a half million entries.
the adresses stand in a 1:N relation together. there' s one ore more
contact person for each adress entry possible.

how to concat the adressinformation to make use of contains clause
that hits across different rows?

e.g. these adresse are linked together:
adress 1: MEGA COMPANY, new york ,United States
X
adress 2: harry potter, hamburg, germany
X
adress 3: harald potter, berlin, germany


I want to make a fulltext query like "Potter mega germany" possible.
This query should hit all 3 adresses.

Any ideas out there?

[Jim Graham]

I'm not sure I understand the question, but I'll give it a shot. If I
completely missed, give a more complete description of your tables and
the relationship between them.

Using the SQL Anywhere 11 demo database, create a text index on all
character columns of Employees.
The following query gives a scored list of employees that contain
lincoln or cornwall.

SELECT score, * FROM Employees
CONTAINS( surname, givenname, street, city, 'lincoln | cornwall' )
ORDER BY score DESC

The next query gives a scored list of departments that have employees
containing lincoln or cornwall. It may unfairly favor large
departments, so you might use AVG rather than SUM.

SELECT SUM(score) as s, departmentid FROM Employees
CONTAINS( surname, givenname, street, city, 'lincoln | cornwall' )
GROUP BY departmentid
ORDER BY s DESC

[Hundredth Monkey]

Yes I' ve tried queries with the OR keyword. The problem with this
methode is that the more keywords I enter, the more results I get.
That should be reversed. :O)
So I have to Use the AND keyword.

I ' ve tried a few selects with LIKE.
the result i expect for my search query should be something like this:

select *,
string(a1.name,a1.street,a1.city..., a2.name,a2.street,a2.city) Q
from ADDRESS a1 join JOINTABLE ... join ADRESS a2 ... where
Q like '%Potter%' and
Q like '%mega%' and
Q like '%Germany%'
but this query is much too slow. :O(

[Jim Graham]

You could use the OR-style full text search to get an initial list of
matches, then use a LIKE condition on the smaller set of data to
reduce the result further. Using LIKE with a leading wildcard (eg.
'%Potter%') will never use an index and will always scan all the rows,
making it very slow on large data sets. Performing a full text search
can reduce the data quickly using a full text index. The LIKE
constraint would only need to be applied to the rows that successfully
matched the CONTAINS expression. You should get the same results as
your LIKE query, but much faster because it will not scan the entire
table.

[Hundredth Monkey]

With your tips I was able to create a cool fulltext search for my
webapp. thanks!