I am experimenting with FULLTEXT searching, using the InnoDB storage engine. I am upgrading from MYISAM to InnoDB and I have noticed that a lot of queries containing LIKE clauses, which were fast with MYISAM, are much slower using InnoDB. Removing the LIKE clause and replacing it with FULLTEXT searching is lightning fast in InnoDB, but seems to come with a couple of gotchas which I hadn’t thought about.

The FULLTEXT parser works out where words start and end by looking at certain characters including spaces ( ), commas (,) and full stops (‘.’). A lot of the rows in my table contain one or more of those characters and it is causing me issues. Imagine my table contains 5 rows with the string ‘test.demo’ and my search query ‘test.demo’ returns results including ‘demo’, ‘string.demo_example’ etc. because it searches for ‘demo’ instead of ‘test.demo’.

I know that is the intended behaviour because this is a text search, not a full stop search! On a practical level, I’d like to to figure out a way to add full stops to the search criteria if possible. Perhaps it is possible to alter the search criteria somewhere. Alternatively, I could create a duplicate column of my data for FULLTEXT searching, stripping out spaces, commas, full stops etc. Then I could strip out these characters from the search string. I have a working demo of this but it seems a lot of work to go to.