CODEWITHSUNDEEP
mysqlfull-text-search
phpSteve
phpSteve

Reputation: 41

Match partial words using MySQL Fulltext search?

I have the following database schema, as an example:

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(11) unsigned NOT NULL,
  `irn` varchar(30) NOT NULL,
  `name` varchar(225) NOT NULL,
  `description` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `name_desc_irn` (`name`,`description`,`irn`),
  FULLTEXT KEY `name` (`name`),
  FULLTEXT KEY `description` (`description`),
  FULLTEXT KEY `irn` (`irn`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

And the following data (assume the data here is simplified, in reality I have over 100,000 records)

id    name        irn          description     
1     Widget A    ABC12345     testing ABC12345
2     Widget B    ABC-12345    the ABC is great

And the following query:

SELECT
    items.id as id,
    items.irn as irn,
    items.name as name,
    (
        ((MATCH (irn) AGAINST ('12345')) * 5) + 
        ((MATCH (name) AGAINST ('12345')) * 4) + 
        ((MATCH (description) AGAINST ('12345')) * 3) + 
        (MATCH(name, description, irn) AGAINST ('12345'))
    ) AS relevance 
FROM
    items 
WHERE 
    MATCH(name, description, irn) AGAINST ('*12345' IN BOOLEAN MODE)
HAVING
    relevance > 0
ORDER BY
    relevance DESC

The second result (irn of ABC-12345) is returned but not the first (ABC12345). Why does it not match ABC12345 but does match ABC-12345? Is there any way to have it match both using Fulltext search? If not, what's the most appropriate way to conduct this type of search where ABC12345 and ABC-12345 are both found when searching for 12345?

Upvotes: 3

Views: 4823

Answers (1)

ghbarratt
ghbarratt

Reputation: 11711

The only information I can give you on the * boolean full-text search operator is that according to the MySQL Manual, "unlike the other operators, it should be appended to the word to be affected."

Your only option for this particular type of query might be to change your where clause to something like:

WHERE irn LIKE '%12345' OR name LIKE '%12345' OR description LIKE '%12345'

Upvotes: 2

Related Questions