CODEWITHSUNDEEP
mysqlfull-text-search
Horse
Horse

Reputation: 3063

mysql fulltext search not returning expected results

So I want to implement a better search functionality using fulltext search, however it is not behaving as expected. it will return no results, or results i would not consider as relevant.

Firstly I have checked:

show variables like 'ft_min%'

...returns as 4

So I have a database full of game titles. A good example of a search is "assassins creed". In the database they are actually stored as "Assassin's Creed" (note apostrophe). So if I do the following query:

SELECT g_name, MATCH(g_name) AGAINST ('assassins creed') AS score FROM tgmp_games WHERE MATCH(g_name) AGAINST ('assassins creed') LIMIT 15

Returns results fine (some below)

...but with the search script, it uses a JqueryUI autocomplete dropdown, and when submitting a query for anything with fewer characters than an entire word it comes back empty

SELECT g_name, MATCH(g_name) AGAINST ('assass') AS score FROM tgmp_games WHERE MATCH(g_name) AGAINST ('assass') LIMIT 15

I have also read that the number of rows in the database can have an effect if its too low, but theres 25k rows in there so it should be fine. What am I doing wrong?

Output of the results I CAN get, but more interested in how I can get the part search term to return some results.

Array
(
    [g_name] => Assassins Creed III
    [score] => 15.406005859375
)

Array
(
    [g_name] => The Witcher 2: Assassins of Kings
    [score] => 8.46316719055176
)

Array
(
    [g_name] => Tenchu: Shadow Assassins
    [score] => 8.46316719055176
)

Array
(
    [g_name] => The Witcher 2: Assassins of Kings - Enhanced Edition
    [score] => 8.27909851074219
)

Array
(
    [g_name] => Assassin\'s Creed
    [score] => 6.84770059585571
)

Array
(
    [g_name] => Assassin\'s Creed III
    [score] => 6.84770059585571
)

Array
(
    [g_name] => Assassin's Creed II
    [score] => 6.84770059585571
)

Array
(
    [g_name] => Assassin's Creed: Revelations ...
    [score] => 6.77157783508301
)

Array
(
    [g_name] => Assassin\'s Creed: Revelations
    [score] => 6.77157783508301
)

Array
(
    [g_name] => Assassin's Creed: Brotherhood
    [score] => 6.77157783508301
)

Array
(
    [g_name] => Assassin's Creed: Bloodlines
    [score] => 6.77157783508301
)

Array
(
    [g_name] => Assassin's Creed II: Discovery
    [score] => 6.77157783508301
)

Array
(
    [g_name] => Assassin\'s Creed III: Liberation
    [score] => 6.77157783508301
)

Array
(
    [g_name] => Assassin's Creed: Altair's Chronicles
    [score] => 6.69712924957275
)

Array
(
    [g_name] => Assassin's Creed II:  Bonfire of the Vanities
    [score] => 6.69712924957275
)

Also the order of this is odd, as "The Witcher 2" is coming above other "Assassin's Creed" games. Not sure if theres some kind of mode I can put it into to avoid getting stuck on punctuation?

Upvotes: 4

Views: 2475

Answers (1)

D-Rock
D-Rock

Reputation: 2676

Take a look at the MySQL documentation for boolean searches. http://dev.mysql.com/doc/refman/5.5/en//fulltext-boolean.html. What you probably want to use is the * operator (similar to % wildcard).

MATCH(g_name) AGAINST ('assass*' IN BOOLEAN MODE)

The number of rows in the table come into affect because of the 50% rule that MySQL applies to fulltext indexes. If a word appears in more than 50% of the rows in the table then MySQL assumes it is too common to provide any value in narrowing down results and it is ignore. This is typically for eliminating words like "the".

Upvotes: 7

Related Questions