How to use full text index for exact matches?

Question

I have a MyISAM MySql table with a full text index, for example:

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 the records with the exact name and brand:

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

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

Answer 1

Something like that:

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

Hope this helps

Answer 2

If you need to find the exact title or brand, you need to use an equal operator with a classic index:

 ALTER TABLE tblsearch ADD INDEX search_idx(title, brand); SELECT id FROM tblsearch WHERE title = 'foo' AND brand = 'bar'; 

Now, if you just need to combine the exact words with the headline and brand:

 SELECT id FROM tblsearch WHERE MATCH(title) AGAINST('+foo' IN BOOLEAN MODE) AND MATCH(brand) AGAINST('+bar' IN BOOLEAN MODE);