Fulltext search problem

Oddish · #1 (**permalink**) 05-30-07, 03:36

I've got a table of companies that I'm trying to search for by name, but it doesn't work like I expected so I'm hoping someone can explain what I'm doing wrong. Here's the query:

Code:

SELECT
    *
FROM
    companies AS com
WHERE
    MATCH (
        com.uc_company_name,
	com.ks_company_name
    ) AGAINST (
        'rör*'
    IN BOOLEAN MODE)

The table has got a fulltext index on the two name columns and when I search for the phrase "rör" like in the query above, I get results like:

Röragenten Björn Halvarsson
Rörfirma K Å Karlsson AB
Bede Rörservice AB

But this company gets left out:

H. Holmbergs Rör AB

Why is that?

Thanks in advance!

Oddish · #2 (**permalink**) 05-30-07, 05:06

Apparently, the host hadn't changed ft_min_word_len to 3 like I asked several weeks ago. I suspect that's the problem.

aschk · #3 (**permalink**) 05-30-07, 05:08

UNION it with a LIKE clause...

aschk · #4 (**permalink**) 05-30-07, 05:14

I'm curious to know why you're using fulltext search anyway. A company name is never going to be HUGE and thus you should have a normal index instead of a fulltext one. This alleviates the need for MATCH and instead you can use a LIKE and get all the right results.

gvee · #5 (**permalink**) 05-30-07, 06:00

I'm more intrigued as to why

Code:

'r&#246;r*'

returns

Bede Rörservice AB

aschk · #6 (**permalink**) 05-30-07, 07:10

Because it's a fulltext search the * acts on words (i.e. seperated by a space) so any word beginning with 'rör' is matched. 'Rörservice' is one word and fulfils the criteria. If it was 'ARörservice' it would fail...

gvee · #7 (**permalink**) 05-30-07, 07:17

*makes a note*
Ohh right!
Thanks mate - I never knew that ^_^

Internet Services

Web Development

Digital Marketing

Consumer Tech