Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    43

    Question Unanswered: Fulltext search problem

    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!

  2. #2
    Join Date
    Mar 2003
    Posts
    43
    Apparently, the host hadn't changed ft_min_word_len to 3 like I asked several weeks ago. I suspect that's the problem.

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    UNION it with a LIKE clause...

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm more intrigued as to why
    Code:
    'rör*'
    returns
    • Bede Rörservice AB
    George
    Home | Blog

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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...

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    *makes a note*
    Ohh right!
    Thanks mate - I never knew that ^_^
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •