CODEWITHSUNDEEP
mysqlfull-text-search
user1537360
user1537360

Reputation: 4821

MySQL Full Text Search odd behaviour

I made a website for a shop that uses an sql table to manage data about their products.

I used full text search so users can search easily through the stock.

The search runs queries to match the term with the product's brand, name and description, like so:

$search_prods = $db_connection->query("

    SELECT      `id`,`name`,`description`
    FROM        `Products`
    WHERE       Match(`brand`) Against('$term')

    UNION

    SELECT      `id`,`name`,`description`
    FROM        `Products`
    WHERE       Match(`description`) Against('$term')

    UNION

    SELECT      `id`,`name`,`description`
    FROM        `Products`
    WHERE       Match(`name`) Against('$term')

    ");

The search works fine except when dealing with the brand 'Pandora'. If I search for a different brand, I get my results. If I search for Pandora, I get results with Pandora in the name and results with Pandora in the description. I can't get any results with Pandora in the brand, though. They do exist! I've tried running the queries inside MySql and I get the same issue.

What is it about searching for 'Pandora' in the column 'Brand' that is causing an issue?? Anyone have any thoughts?

Thanks!

Upvotes: 2

Views: 86

Answers (1)

RandomSeed
RandomSeed

Reputation: 29809

By default, MySQL FullText searches ignore common words:

Words that are present in 50% or more of the rows are considered common and do not match.

If over 50% of the products are of the brand "Pandora", then this is the explanation.

You might want to try adding the IN BOOLEAN MODE modifier in your queries to disable this feature.

By the way, you could (read really should) rewrite your query as follows:

SELECT id, name, description
FROM Products
WHERE MATCH(brand, description, name) AGAINST('$term' IN BOOLEAN MODE)

Upvotes: 1

Related Questions