How to use a full text index for exact matches?

Question

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?

Embonpoint

Answer 1

Something like this:

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

Hope it helps

Mauretania

I need both ?title and ?brand as two different parameters, is that possible? – Embonpoint

you code try combining the title & brand parameters and use it in place of "exact phrase".. – Mauretania

Ok, thanks! Is it ok to use the "like '%exact phrase%'"-part as you do in this query, or will it be bad for performance? The table has about +1 million entries. – Embonpoint

Answer 2

If you need to find the exact title or brand, you need to use the equal operator with a classical 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 match exact words into the title and brand:

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

Foran