Search code examples
mysqlfull-text-searchmyisam

How to use a full text index for exact matches?

I have a MySql MyISAM table with a full text index, like this:

CREATE TABLE `tblsearch` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(100) NOT NULL,
    `brand` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`id`),
    FULLTEXT KEY `index_all` (`title`,`brand`)
) ENGINE=MyISAM AUTO_INCREMENT=1316109 DEFAULT CHARSET=utf8;

Now I need to write a query like this to find all entries with the exact title and brand:

SELECT id FROM tblsearch WHERE title=?title AND brand=?brand;

It is important that the query only give exact matches. I would like to be able to use the full text index that I already have. Is it possible to write the query so it uses the full text index?

Solution

  • Something like this:

    SELECT id 
  FROM tblsearch 
 WHERE MATCH (title, brand) AGAINST ("exact phrase") 
   AND CONCAT(title, ' ', brand) LIKE '%exact phrase%';

    Hope it helps