mysql fulltext index performance issue

Question

I have a table with different fields, in particular name (varchar 255), surname (varchar 255), but actually I used them as name (varchar 90) and surname (varchar 70), considering the maxlength limit.

This table has ~620,000 rows and I'm doing a lot of queries like the following one:

SELECT * FROM table WHERE name LIKE "%word1%" AND surname LIKE "%word2%"

I've added FULLTEXT indexes on both field (tried also one index contains both fields), but the performance is slower than queries without any index.

What I missing?

Thanks in advance

Edit: Following your replies, I show you some results.

Case A. Table without any index Query:

SELECT * 
FROM  `table` 
WHERE  `name` LIKE '%word1%'
AND  `surname` LIKE '%word2%'

executed two times was solved in 0.8870 and 0.8952 sec

Case B. I've added a FULLTEXT index on name and a FULLTEXT index on surname. Query

SELECT * 
FROM table
WHERE match(name) AGAINST ('+word1*' in boolean mode) 
AND match(surname) AGAINST ('+word2*' in boolean mode)

executed two times was solved in 0.9647 and 1.0380 sec

Note: table has InnoDB engine and unfortunatly MySQL is older than 5.6; I cannot convert easily from InnoDB to MyISAM because of foreign keys.

Other ideas? Thanks again

Answer 1

It will be better if you use Mysql (InnoDB)'s Full text search in boolean mode. More Details here

Fulltext search is designed to be efficient in cases like yours and is considerably faster than full scan.

A fulltext search query for your case might look like this :

SELECT * FROM table WHERE match(name) against ('+word1*' in boolean mode) AND match(surname) against ('+word2*' in boolean mode);

Answer 2

"cannot convert easily" -- Sure you can. Make a MyISAM table with nothing but the text column(s) and the id. Then do the MATCH..AGAINST using that table and JOIN (without FKs) to the InnoDB table. The id is already the PRIMARY KEY of the other table, correct? The JOIN just needs an index; the PK qualifies as such; no FK is required.

You do have a 2-column FULLTEXT index, correct? FULLTEXT(name, surname)