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

Possible duplicate of [mysql like performance boost](https://stackoverflow.com/questions/2481528/mysql-like-performance-boost) — Martin Schneider, Jan 09 '18 at 11:55
Of course, `FULLTEXT` indexes don't help make `LIKE '%match%'` WHERE clauses faster. You have to use `FULLTEXT` style WHERE clauses. — O. Jones, Jan 09 '18 at 12:46

score 3 · Answer 1 · edited Jan 09 '18 at 12:16

3

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);

edited Jan 09 '18 at 12:16

Srinivas

1,780
1
14
27

answered Jan 09 '18 at 11:58

saarthak gupta

173
1
11

1

You should be atleast be using MySQL 5.6 or up before this can work for the InnoDB engine, InnoDB engine in lower MySQL versions will not support FULL TEXT indexes.. If you have a lower MySQL version MyISAM engine can also handle FULL TEXT indexes. – Raymond Nijland Jan 09 '18 at 12:00
I've edited my first post. FULLTEXT index, not FULLSCAN index. Sorry for my error. – qweqwe Jan 09 '18 at 12:12
1

agreed @RaymondNijland. The answer assumes this limitation. Thanks for clarifying the MyISAM engine details though :) – saarthak gupta Jan 09 '18 at 12:13
1

@qweqwe please run the `match against` query and please update if the performance improves. – saarthak gupta Jan 09 '18 at 12:15
Just do it on the first post – qweqwe Jan 09 '18 at 13:13

score 1 · Answer 2 · answered Jan 10 '18 at 01:19

"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)

mysql fulltext index performance issue

2 Answers2